set a range thats not allways the same size.
 

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

set a range thats not allways the same size.
 

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

set a range thats not allways the same size.
 

On 27 aug, 11:30, Excel_Expert wrote:
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

set a range thats not allways the same size.
 

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


"Excel_Expert" wrote:


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

set a range thats not allways the same size.
 

On 27 aug, 11:52, p45cal wrote:
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



"Excel_Expert" wrote:

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- Tekst uit oorspronkelijk bericht niet weergeven -

- 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!

set a range thats not allways the same size.
 

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


" wrote:

On 27 aug, 11:52, p45cal wrote:
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



"Excel_Expert" wrote:

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- Tekst uit oorspronkelijk bericht niet weergeven -

- 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!

set a range thats not allways the same size.
 

On 27 aug, 13:04, p45cal wrote:
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



" wrote:
On 27 aug, 11:52, p45cal wrote:
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

"Excel_Expert" wrote:

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- Tekst uit oorspronkelijk bericht niet weergeven -

- 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!- Tekst uit oorspronkelijk bericht niet weergeven -

- Tekst uit oorspronkelijk bericht weergeven -

Cheers!
this is the answer :-)