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

There should be no need to select the range, assign it to an object variable
and act on that.

Dim myRange as Range
Set myRange = loCIM.Range(Cells(1, 1), Cells(1, 4))

Now you can refer to the range by it assigned name myRange

--
Cheers
Nigel



"moonhk" <moon_ils-(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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
>

It works


Set xRange = loCIM.Range(cim.s2D_TaxUsageAddr & cimCnt &
":" & cim.s2D_TexlnAddr & cimCnt)
For Each v In xRange
v.Value = ";"
Next v


Nigel wrote:
> There should be no need to select the range, assign it to an object variable
> and act on that.
>
> Dim myRange as Range
> Set myRange = loCIM.Range(Cells(1, 1), Cells(1, 4))
>
> Now you can refer to the range by it assigned name myRange
>
> --
> Cheers
> Nigel
>
>
>
> "moonhk" <moon_ils-(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > 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
> >