find and paste

[ceemo]

hi im needing some help. I want some VB code to copy range B2:F2 and searching from B6 down paste it in the first blank cell.

[TommySzalapski]

Dim iIndex as Integer

Index = 6

Do While Not Cells(iIndex, 2) = Empty

iIndex = iIndex +1

Loop

'when the loop exits Cells(iIndex, 2) will refer to the first blank cell

[ceemo]

thank you for your reply. m getting debug error on the following line

Do While Not Cells(iIndex, 2) = Empty

[TommySzalapski]

Oops, if you copy-pasted my code change line

Index = 6

to

iIndex = 6

Sorry about that. You're getting the error because iIndex was never set to anything in my errored code.

[ceemo]

im having a little diff still
ive tried to mod your code to this


Range("B2:F2").Copy

Dim iIndex As Integer

iIndex = 6

Do While Not Cells(iIndex, 2) = Empty



iIndex = iIndex + 1

Loop
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False


im not getting any joy though, do you know why its not working

[TommySzalapski]

after the loop exits add

Cells(iIndex,2).select

or change

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

to

Cells(iIndex,2).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False