| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cell Selection for update 55 columns
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(loShe etName) '~~ 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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Cell Selection for update 55 columns
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" wrote in message ups.com... 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(loShe etName) '~~ 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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Cell Selection for update 55 columns
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" wrote in message ups.com... 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(loShe etName) '~~ 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Update cell immediately upon selection from drop down | Excel Programming | |||
| Update cell immediately upon selection from drop down | Excel Programming | |||
| Update cell immediately upon selection from drop down | Excel Programming | |||
| Update cell selection of embedded worksheet in Word | Excel Programming | |||
| Selection.Replace doesn't update the cell value in macro! | Excel Programming | |||
Hybrid Mode
