set a range thats not allways the same size.

[sjoerd.jump]

Hi there,

I could use some help on the following problem.

I have this code to paste a formula to a range.
and it works!

ub Loop1()
Dim rListPaste As Range
Dim i As Long

Set rListPaste = Sheet3.Range("b2", "b1000")

' This loop runs until there is nothing in the next column
rListPaste.Formula = "=VLOOKUP(Sheet1!$A2,FIXEL!$B:$C,2,)"


End Sub

BUT

the range isnt allways till "b1000" it also can be 10 or 100.
so, how do i code this? i only want to copy the code to "where there
is data in collum A"

thanks in advance

kind regards

Sjoerd

 

[Excel_Expert]

Sub Loop1()
Dim rListPaste As Range
Dim i As Long


'Set rListPaste = Sheet3.Range("b2", "b1000")
Set rListPaste = Sheet3.Range("b2", Range("B2").end(XlDown))


' This loop runs until there is nothing in the next column
rListPaste.Formula = "=VLOOKUP(Sheet1!$A2,FIXEL!$B:$C,2,)"

End Sub

 

[sjoerd.jump]

Sub Loop1()
Dim rListPaste As Range
Dim i As Long

'Set rListPaste = Sheet3.Range("b2", "b1000")
Set rListPaste = Sheet3.Range("b2", Range("B2").end(XlDown))

' This loop runs until there is nothing in the next column
rListPaste.Formula = "=VLOOKUP(Sheet1!$A2,FIXEL!$B:$C,2,)"

End Sub

Cheers m8, ill post the code when it works, i made some naste mistakes
there!

tnx

Sjoerd

 

[p45cal]

Not quite Excel_Expert..
OP was looking for filling column B until the last bit of data in column A
(not column B).
Set rListPaste = Sheet3.Range("b2", "b" & Range("A2").End(xlDown).Row)
or
Set rListPaste = Sheet3.Range("b2", "b" & Range("A" &
Rows.Count).End(xlUp).Row)

(that second one's only one line btw)

 

[sjoerd.jump]

Not quite Excel_Expert..
OP was looking for filling column B until the last bit of data in column A
(not column B).
Set rListPaste = Sheet3.Range("b2", "b" & Range("A2").End(xlDown).Row)
or
Set rListPaste = Sheet3.Range("b2", "b" & Range("A" &
Rows.Count).End(xlUp).Row)

(that second one's only one line btw)

--
p45cal








- Tekst uit oorspronkelijk bericht weergeven -

This piece of code works!
thanks very much

this is wat it is now...

Sub Loop1()
Dim rListPaste As Range

Set rListPaste = Sheet3.Range("b2", "b" & Range("A2").End(xlDown).Row)

' This loop runs until there is nothing in the last column
Do
rListPaste.Formula = "=VLOOKUP(Sheet1!$A2,FIXEL!$B:$C,2,)"
ActiveCell.Offset(1, 0).Select
Loop Until IsEmpty(ActiveCell.Offset(0, -1))

End Sub

so i can copy a formula to vlookup cells in col. a.

thanks!

 

[p45cal]

try removing the following 3 lines and see if it still does what you want:
Do
ActiveCell.Offset(1, 0).Select
Loop Until IsEmpty(ActiveCell.Offset(0, -1))

 

[sjoerd.jump]

try removing the following 3 lines and see if it still does what you want:
Do
ActiveCell.Offset(1, 0).Select
Loop Until IsEmpty(ActiveCell.Offset(0, -1))
--
p45cal












- Tekst uit oorspronkelijk bericht weergeven -

Cheers!
this is the answer