M
moonhk
Dear ALL
I have one worksheet have 55 columns need to update. More of columns
value are "-" or ";"
How to using range selection to change those value ?
I try Range(Cells(1, 1), Cells(1, 4)).Select ok , just of active
worksheet(loSheet)
but loCIM.Range(Cells(1, 1), Cells(1, 4)).Select not ok, due to loCIM
is not active. Already active loSheet.
Do you know why if loCIM worksheet is not active, it is not able to
select the range ?
So, I using below coding to check the value
For iCnt = 0 To cim.getCOL_RANGE '~~ Mass Change
loCIM.Range(cim.s2BatchAddr & cimCnt).Offset(0, iCnt) =
";"
Next
Option Explicit
'~~ 2006/11/14
Public cim As New clsCIM
Public Sub Tst_Build_CIM()
Call Build_CIM("SH-451455")
End Sub
Public Sub Build_CIM(loShName As String)
On Error Resume Next
Dim loBook As Workbook
Dim loSheet As Worksheet
Dim loBookName As String
Dim loSheetName As String
Dim loCIM As Worksheet
Dim cnt, cimCnt, iCnt, LineCnt As Long
Dim vRange, vObject
Dim VoucherTotal, MarkupAmt, BTAmt, VatAmt, Amt As Double
cim.init
site.getConfig (loShName)
MsgBox site.ProjectCodeText
loBookName = Application.ActiveWorkbook.Name
'loSheetName = Application.ActiveSheet.Name
loSheetName = loShName
Application.StatusBar = "Processing..." & loSheetName
'~~ MsgBox loSheetName
Set loCIM =
Application.Workbooks("apvomt_v5.xls").Worksheets("Detail")
If loCIM Is Nothing Then
MsgBox "Workbook not opened apvomt_v5.xls or " & Chr(13) & _
"Worsksheets 'Detail' not found.", vbCritical
Exit Sub
End If
Set loSheet =
Application.Workbooks(loBookName).Worksheets(loSheetName)
'~~ Temp
If loSheet Is Nothing Then
MsgBox "Workbook not opened " & loBookName & " " & loSheetName,
vbCritical
Exit Sub
End If
'~~ Temp
site.getConfig ("SH-451455")
'~~return to normal error handling
On Error GoTo 0
cnt = 200
cimCnt = 7
'~~MsgBox site.s1dnAddr & "CIM=" & cim.s2BatchAddr
Do
'~~ Setup Invoice Value
Debug.Print loSheet.Range(site.s1dnAddr & cnt)
If VBA.Trim(loSheet.Range(site.s1dnAddr & cnt)) <> "" Then
'~~ Check Header
If VBA.Trim(loSheet.Range(site.s1lpAddr & cnt).Value) = "Header"
Then
For iCnt = 0 To cim.getCOL_RANGE '~~ Mass Change
loCIM.Range(cim.s2BatchAddr & cimCnt).Offset(0, iCnt) =
"-"
Next
loCIM.Range(cim.s2BatchAddr & cimCnt).Value = ""
loCIM.Range(cim.s2VoucherAddr & cimCnt).Value = ""
loCIM.Range(cim.s2POAddr & cimCnt).Value = "."
loCIM.Range(cim.s2SupplierAddr & cimCnt).Value = "0218"
loCIM.Range(cim.s2InvoiceAddr & cimCnt).Value = _
"'" + loSheet.Range(site.s1dnAddr & cnt).Value
'~~ Taxable
loCIM.Range(cim.s2TaxableAddr & cimCnt).Value = "n"
'~~ Detail area
loCIM.Range(cim.s2D_TaxAddr & cimCnt).Value = "n"
loCIM.Range(cim.s2D_nextlnAddr & cimCnt).Value = ";"
'~~ Get Voucher Total and MISC amt
VoucherTotal = loSheet.Range(site.s1tdAddr & cnt).Value
' MarkupAmt = loSheet.Range(site.s1smAddr).Value
' BTAmt = loSheet.Range(site.s1btAddr).Value
' VatAmt = loSheet.Range(site.s1vatAddr).Value
LineCnt = 1
'~~ Touch provious line
If cimCnt <> 7 Then
loCIM.Range(cim.s2D_nextlnAddr & cimCnt - 1).Value =
"."
loCIM.Range(cim.s3D_VoucherAddr & cimCnt - 1).Value =
"."
loCIM.Range(cim.s3D_BatchAddr & cimCnt - 1).Value = "."
loCIM.Range(cim.s3D_hasNextLnAddr & cimCnt - 1).Value =
""
End If
Else
' vObject = cim.s2BatchAddr & cimCnt & ":" & cim.s3D_BatchAddr &
cimCnt
' vObject = Chr(34) + vObject + Chr(34)
'Debug.Print vObject
' loSheet.Range("AM84:AO84").Select
' loCIM.Range(Cells(1, 1), Cells(1, 4)).Select
For iCnt = 0 To cim.getCOL_RANGE '~~ Mass Change
loCIM.Range(cim.s2BatchAddr & cimCnt).Offset(0, iCnt) =
";"
Next
loCIM.Range(cim.s2D_TaxAddr & cimCnt).Value = "n"
LineCnt = LineCnt + 1
End If
'~~Debug.Print site.MfgproAccountAddr & Str(cnt)
loCIM.Range(cim.s2D_lnAddr & cimCnt).Value = LineCnt
loCIM.Range(cim.s2D_AccAddr & cimCnt).Value = _
loSheet.Range(site.s1maAddr & cnt).Value
loCIM.Range(cim.s2D_ccAddr & cimCnt).Value = _
loSheet.Range(site.s1mcAddr & cnt).Value
'~~ Amount
Amt = Round(loSheet.Range(site.s1caAddr & cnt).Value, 2)
loCIM.Range(cim.s2D_AmountAddr & cimCnt).Value = Amt
loCIM.Range(cim.s2D_projectAddr & cimCnt).Value = _
site.ProjectCodeText
loCIM.Range(cim.s2D_entyAddr & cimCnt).Value = "-"
loCIM.Range(cim.s2D_DescAddr & cimCnt).Value = "-"
loCIM.Range(cim.s2D_RefAddr & cimCnt).Value = "."
loCIM.Range(cim.s2D_nextlnAddr & cimCnt).Value = ";"
'~~ Have Next Detail Line
loCIM.Range(cim.s3D_hasNextLnAddr & cimCnt).Value = "~"
'~~ Update counter
cimCnt = cimCnt + 1
If (cnt Mod 10) = 0 Then
Debug.Print "cnt=" & Str(cnt) & ", " & _
cim.s2InvoiceAddr & " " & _
"Site.s1dnAddr= " & site.s1dnAddr & " " & _
",Value(s1dn) = " & loSheet.Range(site.s1dnAddr &
cnt).Value
End If
Application.StatusBar = cnt
End If
cnt = cnt - 1
Loop While cnt > 2
MsgBox cnt
End Sub
Option Explicit
'~~ Class Name : clsCIM
'~~ 2006/11/14
Const COL_RANGE = 55
Public s2BatchAddr As String
Public s2ControlAddr As String
Public s2VoucherAddr As String
Public s2POAddr As String
Public s2Control2Addr As String
Public s2SupplierAddr As String
Public s2EffectAddr As String
Public s2TaxdateAddr As String
Public s2ShiptoAddr As String
Public s2CurrencyAddr As String
Public s2BankAddr As String
Public s2InvoiceAddr As String
Public s2dateAddr As String
Public s2TermAddr As String
Public s2DiscDateAddr As String
Public s2DueDateAddr As String
Public s2ExpDateAddr As String
Public s2AcctAddr As String
Public s2ccAddr As String
Public s2DiscAddr As String
Public s2DiscCCAddr As String
Public s2EntitAddr As String
Public s2RemarkAddr As String
Public s2SuppBkAddr As String
Public s2SepCKAddr As String
Public s2TypeAddr As String
Public s2ckformAddr As String
Public s2PrepayAmtAddr As String
Public s2NonDiscAmtAddr As String
Public s2DayBookAddr As String
Public s2ExRateAddr As String
Public s2ExchRateAddr As String
Public s2TaxUsageAddr As String
Public s2TaxEnvAddr As String
Public s2TaxClassAddr As String
Public s2TaxableAddr As String
Public s2TaxInAddr As String
Public s2D_lnAddr As String
Public s2D_AccAddr As String
Public s2D_ccAddr As String
Public s2D_entyAddr As String
Public s2D_projectAddr As String
Public s2D_TaxAddr As String
Public s2D_TaxUsageAddr As String
Public s2D_TaxCCAddr As String
Public s2D_TaxableAddr As String
Public s2D_TexlnAddr As String
Public s2D_DescAddr As String
Public s2D_AmountAddr As String
Public s2D_RefAddr As String
Public s2D_nextlnAddr As String
Public s3D_viewAddr As String
Public s3D_HoldAmtAddr As String
Public s3D_ConfirmAddr As String
Public s3D_assigeAddr As String
Public s3D_VoucherAddr As String
Public s3D_BatchAddr As String
Public s3D_hasNextLnAddr As String
Public Function getCOL_RANGE() As Long
getCOL_RANGE = COL_RANGE
End Function
Public Sub init()
s2BatchAddr = "A"
s2ControlAddr = "B"
s2VoucherAddr = "C"
s2POAddr = "D"
s2Control2Addr = "E"
s2SupplierAddr = "F"
s2EffectAddr = "G"
s2TaxdateAddr = "H"
s2ShiptoAddr = "I"
s2CurrencyAddr = "J"
s2BankAddr = "K"
s2InvoiceAddr = "L"
s2dateAddr = "M"
s2TermAddr = "N"
s2DiscDateAddr = "O"
s2DueDateAddr = "P"
s2ExpDateAddr = "Q"
s2AcctAddr = "R"
s2ccAddr = "S"
s2DiscAddr = "T"
s2DiscCCAddr = "U"
s2EntitAddr = "V"
s2RemarkAddr = "W"
s2SuppBkAddr = "X"
s2SepCKAddr = "Y"
s2TypeAddr = "Z"
s2ckformAddr = "AA"
s2PrepayAmtAddr = "AB"
s2NonDiscAmtAddr = "AC"
s2DayBookAddr = "AD"
s2ExRateAddr = "AE"
s2TaxUsageAddr = "AF"
s2TaxEnvAddr = "AG"
s2TaxClassAddr = "AH"
s2TaxableAddr = "AI"
s2TaxInAddr = "AJ"
s2D_lnAddr = "AK"
s2D_AccAddr = "AL"
s2D_ccAddr = "AM"
s2D_entyAddr = "AN"
s2D_projectAddr = "AO"
s2D_TaxAddr = "AP"
s2D_TaxUsageAddr = "AQ"
s2D_TaxCCAddr = "AR"
s2D_TaxableAddr = "AS"
s2D_TexlnAddr = "AT"
s2D_DescAddr = "AU"
s2D_AmountAddr = "AV"
s2D_RefAddr = "AW"
s2D_nextlnAddr = "AX"
s3D_viewAddr = "AY"
s3D_HoldAmtAddr = "AZ"
s3D_ConfirmAddr = "BA"
s3D_assigeAddr = "BB"
s3D_VoucherAddr = "BC"
s3D_BatchAddr = "BD"
s3D_hasNextLnAddr = "BE"
End Sub
I have one worksheet have 55 columns need to update. More of columns
value are "-" or ";"
How to using range selection to change those value ?
I try Range(Cells(1, 1), Cells(1, 4)).Select ok , just of active
worksheet(loSheet)
but loCIM.Range(Cells(1, 1), Cells(1, 4)).Select not ok, due to loCIM
is not active. Already active loSheet.
Do you know why if loCIM worksheet is not active, it is not able to
select the range ?
So, I using below coding to check the value
For iCnt = 0 To cim.getCOL_RANGE '~~ Mass Change
loCIM.Range(cim.s2BatchAddr & cimCnt).Offset(0, iCnt) =
";"
Next
Option Explicit
'~~ 2006/11/14
Public cim As New clsCIM
Public Sub Tst_Build_CIM()
Call Build_CIM("SH-451455")
End Sub
Public Sub Build_CIM(loShName As String)
On Error Resume Next
Dim loBook As Workbook
Dim loSheet As Worksheet
Dim loBookName As String
Dim loSheetName As String
Dim loCIM As Worksheet
Dim cnt, cimCnt, iCnt, LineCnt As Long
Dim vRange, vObject
Dim VoucherTotal, MarkupAmt, BTAmt, VatAmt, Amt As Double
cim.init
site.getConfig (loShName)
MsgBox site.ProjectCodeText
loBookName = Application.ActiveWorkbook.Name
'loSheetName = Application.ActiveSheet.Name
loSheetName = loShName
Application.StatusBar = "Processing..." & loSheetName
'~~ MsgBox loSheetName
Set loCIM =
Application.Workbooks("apvomt_v5.xls").Worksheets("Detail")
If loCIM Is Nothing Then
MsgBox "Workbook not opened apvomt_v5.xls or " & Chr(13) & _
"Worsksheets 'Detail' not found.", vbCritical
Exit Sub
End If
Set loSheet =
Application.Workbooks(loBookName).Worksheets(loSheetName)
'~~ Temp
If loSheet Is Nothing Then
MsgBox "Workbook not opened " & loBookName & " " & loSheetName,
vbCritical
Exit Sub
End If
'~~ Temp
site.getConfig ("SH-451455")
'~~return to normal error handling
On Error GoTo 0
cnt = 200
cimCnt = 7
'~~MsgBox site.s1dnAddr & "CIM=" & cim.s2BatchAddr
Do
'~~ Setup Invoice Value
Debug.Print loSheet.Range(site.s1dnAddr & cnt)
If VBA.Trim(loSheet.Range(site.s1dnAddr & cnt)) <> "" Then
'~~ Check Header
If VBA.Trim(loSheet.Range(site.s1lpAddr & cnt).Value) = "Header"
Then
For iCnt = 0 To cim.getCOL_RANGE '~~ Mass Change
loCIM.Range(cim.s2BatchAddr & cimCnt).Offset(0, iCnt) =
"-"
Next
loCIM.Range(cim.s2BatchAddr & cimCnt).Value = ""
loCIM.Range(cim.s2VoucherAddr & cimCnt).Value = ""
loCIM.Range(cim.s2POAddr & cimCnt).Value = "."
loCIM.Range(cim.s2SupplierAddr & cimCnt).Value = "0218"
loCIM.Range(cim.s2InvoiceAddr & cimCnt).Value = _
"'" + loSheet.Range(site.s1dnAddr & cnt).Value
'~~ Taxable
loCIM.Range(cim.s2TaxableAddr & cimCnt).Value = "n"
'~~ Detail area
loCIM.Range(cim.s2D_TaxAddr & cimCnt).Value = "n"
loCIM.Range(cim.s2D_nextlnAddr & cimCnt).Value = ";"
'~~ Get Voucher Total and MISC amt
VoucherTotal = loSheet.Range(site.s1tdAddr & cnt).Value
' MarkupAmt = loSheet.Range(site.s1smAddr).Value
' BTAmt = loSheet.Range(site.s1btAddr).Value
' VatAmt = loSheet.Range(site.s1vatAddr).Value
LineCnt = 1
'~~ Touch provious line
If cimCnt <> 7 Then
loCIM.Range(cim.s2D_nextlnAddr & cimCnt - 1).Value =
"."
loCIM.Range(cim.s3D_VoucherAddr & cimCnt - 1).Value =
"."
loCIM.Range(cim.s3D_BatchAddr & cimCnt - 1).Value = "."
loCIM.Range(cim.s3D_hasNextLnAddr & cimCnt - 1).Value =
""
End If
Else
' vObject = cim.s2BatchAddr & cimCnt & ":" & cim.s3D_BatchAddr &
cimCnt
' vObject = Chr(34) + vObject + Chr(34)
'Debug.Print vObject
' loSheet.Range("AM84:AO84").Select
' loCIM.Range(Cells(1, 1), Cells(1, 4)).Select
For iCnt = 0 To cim.getCOL_RANGE '~~ Mass Change
loCIM.Range(cim.s2BatchAddr & cimCnt).Offset(0, iCnt) =
";"
Next
loCIM.Range(cim.s2D_TaxAddr & cimCnt).Value = "n"
LineCnt = LineCnt + 1
End If
'~~Debug.Print site.MfgproAccountAddr & Str(cnt)
loCIM.Range(cim.s2D_lnAddr & cimCnt).Value = LineCnt
loCIM.Range(cim.s2D_AccAddr & cimCnt).Value = _
loSheet.Range(site.s1maAddr & cnt).Value
loCIM.Range(cim.s2D_ccAddr & cimCnt).Value = _
loSheet.Range(site.s1mcAddr & cnt).Value
'~~ Amount
Amt = Round(loSheet.Range(site.s1caAddr & cnt).Value, 2)
loCIM.Range(cim.s2D_AmountAddr & cimCnt).Value = Amt
loCIM.Range(cim.s2D_projectAddr & cimCnt).Value = _
site.ProjectCodeText
loCIM.Range(cim.s2D_entyAddr & cimCnt).Value = "-"
loCIM.Range(cim.s2D_DescAddr & cimCnt).Value = "-"
loCIM.Range(cim.s2D_RefAddr & cimCnt).Value = "."
loCIM.Range(cim.s2D_nextlnAddr & cimCnt).Value = ";"
'~~ Have Next Detail Line
loCIM.Range(cim.s3D_hasNextLnAddr & cimCnt).Value = "~"
'~~ Update counter
cimCnt = cimCnt + 1
If (cnt Mod 10) = 0 Then
Debug.Print "cnt=" & Str(cnt) & ", " & _
cim.s2InvoiceAddr & " " & _
"Site.s1dnAddr= " & site.s1dnAddr & " " & _
",Value(s1dn) = " & loSheet.Range(site.s1dnAddr &
cnt).Value
End If
Application.StatusBar = cnt
End If
cnt = cnt - 1
Loop While cnt > 2
MsgBox cnt
End Sub
Option Explicit
'~~ Class Name : clsCIM
'~~ 2006/11/14
Const COL_RANGE = 55
Public s2BatchAddr As String
Public s2ControlAddr As String
Public s2VoucherAddr As String
Public s2POAddr As String
Public s2Control2Addr As String
Public s2SupplierAddr As String
Public s2EffectAddr As String
Public s2TaxdateAddr As String
Public s2ShiptoAddr As String
Public s2CurrencyAddr As String
Public s2BankAddr As String
Public s2InvoiceAddr As String
Public s2dateAddr As String
Public s2TermAddr As String
Public s2DiscDateAddr As String
Public s2DueDateAddr As String
Public s2ExpDateAddr As String
Public s2AcctAddr As String
Public s2ccAddr As String
Public s2DiscAddr As String
Public s2DiscCCAddr As String
Public s2EntitAddr As String
Public s2RemarkAddr As String
Public s2SuppBkAddr As String
Public s2SepCKAddr As String
Public s2TypeAddr As String
Public s2ckformAddr As String
Public s2PrepayAmtAddr As String
Public s2NonDiscAmtAddr As String
Public s2DayBookAddr As String
Public s2ExRateAddr As String
Public s2ExchRateAddr As String
Public s2TaxUsageAddr As String
Public s2TaxEnvAddr As String
Public s2TaxClassAddr As String
Public s2TaxableAddr As String
Public s2TaxInAddr As String
Public s2D_lnAddr As String
Public s2D_AccAddr As String
Public s2D_ccAddr As String
Public s2D_entyAddr As String
Public s2D_projectAddr As String
Public s2D_TaxAddr As String
Public s2D_TaxUsageAddr As String
Public s2D_TaxCCAddr As String
Public s2D_TaxableAddr As String
Public s2D_TexlnAddr As String
Public s2D_DescAddr As String
Public s2D_AmountAddr As String
Public s2D_RefAddr As String
Public s2D_nextlnAddr As String
Public s3D_viewAddr As String
Public s3D_HoldAmtAddr As String
Public s3D_ConfirmAddr As String
Public s3D_assigeAddr As String
Public s3D_VoucherAddr As String
Public s3D_BatchAddr As String
Public s3D_hasNextLnAddr As String
Public Function getCOL_RANGE() As Long
getCOL_RANGE = COL_RANGE
End Function
Public Sub init()
s2BatchAddr = "A"
s2ControlAddr = "B"
s2VoucherAddr = "C"
s2POAddr = "D"
s2Control2Addr = "E"
s2SupplierAddr = "F"
s2EffectAddr = "G"
s2TaxdateAddr = "H"
s2ShiptoAddr = "I"
s2CurrencyAddr = "J"
s2BankAddr = "K"
s2InvoiceAddr = "L"
s2dateAddr = "M"
s2TermAddr = "N"
s2DiscDateAddr = "O"
s2DueDateAddr = "P"
s2ExpDateAddr = "Q"
s2AcctAddr = "R"
s2ccAddr = "S"
s2DiscAddr = "T"
s2DiscCCAddr = "U"
s2EntitAddr = "V"
s2RemarkAddr = "W"
s2SuppBkAddr = "X"
s2SepCKAddr = "Y"
s2TypeAddr = "Z"
s2ckformAddr = "AA"
s2PrepayAmtAddr = "AB"
s2NonDiscAmtAddr = "AC"
s2DayBookAddr = "AD"
s2ExRateAddr = "AE"
s2TaxUsageAddr = "AF"
s2TaxEnvAddr = "AG"
s2TaxClassAddr = "AH"
s2TaxableAddr = "AI"
s2TaxInAddr = "AJ"
s2D_lnAddr = "AK"
s2D_AccAddr = "AL"
s2D_ccAddr = "AM"
s2D_entyAddr = "AN"
s2D_projectAddr = "AO"
s2D_TaxAddr = "AP"
s2D_TaxUsageAddr = "AQ"
s2D_TaxCCAddr = "AR"
s2D_TaxableAddr = "AS"
s2D_TexlnAddr = "AT"
s2D_DescAddr = "AU"
s2D_AmountAddr = "AV"
s2D_RefAddr = "AW"
s2D_nextlnAddr = "AX"
s3D_viewAddr = "AY"
s3D_HoldAmtAddr = "AZ"
s3D_ConfirmAddr = "BA"
s3D_assigeAddr = "BB"
s3D_VoucherAddr = "BC"
s3D_BatchAddr = "BD"
s3D_hasNextLnAddr = "BE"
End Sub