route217
asked on
Cannot get macro to auto fill correct number of entries
Hi Experts using Excel 213
i have the following VBA code which to a certain extent works kind of fine....
I am trying to populate a formula is cell B8 down based on the number of entries in column B2 onwards sheet "Unique List" into B8 and fill down...
so if we have a count of 83 in B2:B85 then autofill said formula in cell B8 (inc B8) 83 times..
i have the following VBA code which to a certain extent works kind of fine....
I am trying to populate a formula is cell B8 down based on the number of entries in column B2 onwards sheet "Unique List" into B8 and fill down...
so if we have a count of 83 in B2:B85 then autofill said formula in cell B8 (inc B8) 83 times..
Sub Autopopulate()
Dim Rng As Long, Rng1 As Range
Application.ScreenUpdating = False
Rng = Sheets("Unique List").Range("B" & Rows.Count).End(xlUp).Row ' the range of entries
With Sheets("Broker Level")
.Range("B8:B" & Rng).FillDown ' the range where the formula is in the first cell
End With
Application.ScreenUpdating = True
End Sub
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
sorry no...
because as per original code b is in sheet unique and populate into worksheet Broker Level...hmm
appreciate the feedback
because as per original code b is in sheet unique and populate into worksheet Broker Level...hmm
appreciate the feedback
ASKER
ok i see where the error is.....but not sure how to correct it..
If you change to . Range("A2:A" & Rng).Filldown works fine.....
but my starting point is offset to B8...
If you change to . Range("A2:A" & Rng).Filldown works fine.....
but my starting point is offset to B8...
In that case qualify the Range("B2").Value with the sheet reference....
Like this...
Sheets("Unique").Range("B2 ").Value
Rng in your original code is a Long variable while Rng in the code I suggested it's a Range variable.
Like this...
Sheets("Unique").Range("B2
Rng in your original code is a Long variable while Rng in the code I suggested it's a Range variable.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
you are using the count of the Unique List as the ending part of the Range in the Broker level range
So for example if there are 83 entries in Unique list (Rng = 83) then your fill down area (range) in Broker Level is B8:B83 which is not what you want.
You want B8 +83 rows down, correct?
Which is B8:B91 (that's why I plus 8 your range)
So for example if there are 83 entries in Unique list (Rng = 83) then your fill down area (range) in Broker Level is B8:B83 which is not what you want.
You want B8 +83 rows down, correct?
Which is B8:B91 (that's why I plus 8 your range)
ASKER
Thank u and thank u again..experts
You're welcome..
glad we could help
ASKER