delete vbcode dynamically

[ilyaskazi]

How to delete vbcode (coded in ThisWorkbook) of targetworkbook from activeworkbook dynamically ??

[keepITcool]

be aware that that many users have DISABLED
"Trust Access to Visual Basic Project".

if not enabled then all reading/writing to VBProject will fail.
if enabled then:

With Workbooks("myTarget").VBProject. _
VBComponents("thisworkbook").CodeModule
.DeleteLines 1, .CountOfLines
End With


--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


ilyaskazi wrote :

>
> How to delete vbcode (coded in ThisWorkbook) of targetworkbook from
> activeworkbook dynamically ??

[Bob Phillips]

Sub DeleteProcedure()
Dim oVBCodeMod As Object

Set oVBCodeMod =
Workbooks("Book1").VBProject.VBComponents("Thisworkbook").CodeModule
With oVBCodeMod
.DeleteLines 1, .countofLines
End With

End Sub


--
HTH

Bob Phillips

"ilyaskazi" <[email protected]> wrote
in message news:[email protected]...
>
> How to delete vbcode (coded in ThisWorkbook) of targetworkbook from
> activeworkbook dynamically ??
>
>
> --
> ilyaskazi
> ------------------------------------------------------------------------
> ilyaskazi's Profile:
http://www.excelforum.com/member.php...o&userid=23969
> View this thread: http://www.excelforum.com/showthread...hreadid=379273
>

[ilyaskazi]

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:

Please Login or Register  to view this content.

[ilyaskazi]

for further assistance check here:

also posted: http://www.vbaexpress.com/forum/showthread.php?t=3629

[keepITcool]

afaik you cant/shouldnt create event procs like that.
you need to use CreateEventProc method
from Extensibility library.

chip pearson has more info on his site.

--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


ilyaskazi wrote :

>
> 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("ThisWorkbook").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 --------------------

[Bob Phillips]

replied in VBAExpress

--
HTH

Bob Phillips

"ilyaskazi" <[email protected]> wrote
in message news:[email protected]...
>
> 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("ThisWorkbook").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
>

[Bob Phillips]

Shouldn't perhaps, but are you sure about can't.

I just tried this and it seems to work fine

With ThisWorkbook.VBProject.VBComponents("ThisWorkbook").CodeModule
.InsertLines .CountOfLines + 1, _
"Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal
Target As Range)" & vbNewLine & _
"MsgBox ""hello""" & vbNewLine & _
"End Sub"

End With

Bob

"keepITcool" <[email protected]> wrote in message
news:[email protected]...
>
> afaik you cant/shouldnt create event procs like that.
> you need to use CreateEventProc method
> from Extensibility library.
>
> chip pearson has more info on his site.
>
> --
> keepITcool
> | www.XLsupport.com | keepITcool chello nl | amsterdam
>
>
> ilyaskazi wrote :
>
> >
> > 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("ThisWorkbook").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 --------------------

[keepITcool]

afaik should have been afair(emember)
re can't
you demonstrate you can..
thus proving i remembered wrongly.

re should't
maybe. i'm confused now

important:
the newly created events could interfere with running code.
so disable events before adding the code lines.

syntax..
I suggest OP uses a string variable. Set that. Then pump it.
makes for more readable code anyway.

cheerz!

--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Bob Phillips wrote :

> Shouldn't perhaps, but are you sure about can't.
>
> I just tried this and it seems to work fine
>
> With
> ThisWorkbook.VBProject.VBComponents("ThisWorkbook").CodeModule
> .InsertLines .CountOfLines + 1, _ "Private Sub
> Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As
> Range)" & vbNewLine & _ "MsgBox ""hello""" & vbNewLine & _
> "End Sub"
>
> End With
>
> Bob
>
> "keepITcool" <[email protected]> wrote in message
> news:[email protected]...
> >
> > afaik you cant/shouldnt create event procs like that.
> > you need to use CreateEventProc method
> > from Extensibility library.
> >
> > chip pearson has more info on his site.
> >
> > --
> > keepITcool
> > > www.XLsupport.com | keepITcool chello nl | amsterdam
> >
> >
> > ilyaskazi wrote :
> >
> > >
> > > 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("ThisWorkbook").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 --------------------

[Bob Phillips]

"keepITcool" <[email protected]> wrote in message
news:[email protected]...
> re should't
> maybe. i'm confused now

You may be confused, but I agree absolutely with you. There is a method, so
use it.

> important:
> the newly created events could interfere with running code.
> so disable events before adding the code lines.

Excellent point!

> syntax..
> I suggest OP uses a string variable. Set that. Then pump it.
> makes for more readable code anyway.

Agree again. That is how I do it. It is also how I create formulas in VBA,
it also makes debugging far easier.