ExcelBanter - View Single Post

Bob Phillips[_7_]

replied in VBAExpress

--
HTH

Bob Phillips

"ilyaskazi" wrote
in message ...

wow.... perfect. thanku..

Also i m trying to put some vbcode dynamically in target wrkbk as given
below...
but compile error msg pops-up for:

Code:
--------------------

With ActiveWorkbook.VBProject.VBComponents("ThisWorkboo k").CodeModule
.InsertLines .CountOfLines + 1, _
"Private Sub Workbook_BeforeClose(Cancel As Boolean)" & Chr(13) & _
"Dim lRowIndex As Long" & vbNewLine & _
"Dim lColIndex As Integer" & vbNewLine & _
"' RTA, OTA" & vbNewLine & _
"With Worksheets("Rules")" & vbNewLine & _
"For lRowIndex = 2 To 65535" & vbNewLine & _
"If .Cells(lRowIndex, 9).value = "" Then" & vbNewLine & _
"Exit For" & vbNewLine & _
"Else" & vbNewLine & _
"For lColIndex = 16 To 19" & vbNewLine & _
"If .Cells(lRowIndex, lColIndex).value = "" Then" & vbNewLine & _
".Cells(lRowIndex, lColIndex).value = "0"" & vbNewLine & _
"End If" & vbNewLine & _
"Next lColIndex" & vbNewLine & _
"End If" & vbNewLine & _
"Next lRowIndex" & vbNewLine & _
"End With" & vbNewLine & _
"With Worksheets("Validity")" & vbNewLine & _
"For lRowIndex = 2 To 65535" & vbNewLine & _
"If .Cells(lRowIndex, 17).value = "" Then" & vbNewLine & _
"Exit For" & vbNewLine & _
"Else" & vbNewLine & _
"For lColIndex = 17 To 38" & vbNewLine &_
"If .Cells(lRowIndex, lColIndex).value = "" Then" & vbNewLine & _
"' do nothing" & vbNewLine & _
"Else" & vbNewLine & _
".Cells(lRowIndex, lColIndex).value = "'" & .Cells(lRowIndex,
lColIndex).value" & vbNewLine & _
"End If" & vbNewLine & _
"Next lColIndex" & vbNewLine & _
"If .Cells(lRowIndex, 40).value < "" Then .Cells(lRowIndex, 40).value =
"'" & .Cells(lRowIndex, 40).value" & vbNewLine & _
"If .Cells(lRowIndex, 41).value < "" Then .Cells(lRowIndex, 41).value =
"'" & .Cells(lRowIndex, 41).value" & vbNewLine & _
"If .Cells(lRowIndex, 43).value < "" Then .Cells(lRowIndex, 43).value =
"'" & .Cells(lRowIndex, 43).value" & vbNewLine & _
"If .Cells(lRowIndex, 44).value < "" Then .Cells(lRowIndex, 44).value =
"'" & .Cells(lRowIndex, 44).value" & vbNewLine & _
"If .Cells(lRowIndex, 46).value < "" Then .Cells(lRowIndex, 46).value =
"'" & .Cells(lRowIndex, 46).value" & vbNewLine & _
"If .Cells(lRowIndex, 47).value < "" Then .Cells(lRowIndex, 47).value =
"'" & .Cells(lRowIndex, 47).value" & vbNewLine & _
"If .Cells(lRowIndex, 49).value < "" Then .Cells(lRowIndex, 48).value =
"'" & .Cells(lRowIndex, 49).value" & vbNewLine & _
"If .Cells(lRowIndex, 50).value < "" Then .Cells(lRowIndex, 50).value =
"'" & .Cells(lRowIndex, 50).value" & vbNewLine & _
"If .Cells(lRowIndex, 52).value < "" Then .Cells(lRowIndex, 52).value =
"'" & .Cells(lRowIndex, 52).value" & vbNewLine & _
"If .Cells(lRowIndex, 53).value < "" Then .Cells(lRowIndex, 53).value =
"'" & .Cells(lRowIndex, 53).value" & vbNewLine & _
"If .Cells(lRowIndex, 55).value < "" Then .Cells(lRowIndex, 55).value =
"'" & .Cells(lRowIndex, 55).value" & vbNewLine & _
"If .Cells(lRowIndex, 56).value < "" Then .Cells(lRowIndex, 56).value =
"'" & .Cells(lRowIndex, 56).value" & vbNewLine & _
"If .Cells(lRowIndex, 58).value < "" Then .Cells(lRowIndex, 58).value =
"'" & .Cells(lRowIndex, 58).value" & vbNewLine & _
"If .Cells(lRowIndex, 59).value < "" Then .Cells(lRowIndex, 59).value =
"'" & .Cells(lRowIndex, 59).value" & vbNewLine & _
"If .Cells(lRowIndex, 61).value < "" Then .Cells(lRowIndex, 61).value =
"'" & .Cells(lRowIndex, 61).value" & vbNewLine & _
"If .Cells(lRowIndex, 62).value < "" Then .Cells(lRowIndex, 62).value =
"'" & .Cells(lRowIndex, 62).value" & vbNewLine & _
"If .Cells(lRowIndex, 64).value < "" Then .Cells(lRowIndex, 64).value =
"'" & .Cells(lRowIndex, 64).value" & vbNewLine & _
"If .Cells(lRowIndex, 65).value < "" Then .Cells(lRowIndex, 65).value =
"'" & .Cells(lRowIndex, 65).value" & vbNewLine & _
"End If" & vbNewLine &_
"Next lRowIndex" & vbNewLine & _
"End With" & vbNewLine & _
"End Sub"
End With

--------------------

--
ilyaskazi
------------------------------------------------------------------------
ilyaskazi's Profile:
http://www.excelforum.com/member.php...o&userid=23969
View this thread: http://www.excelforum.com/showthread...hreadid=379273