How to adjust VBA for VLOOKUP to cover entire range of data

[bdouglas1011]

I would like to adjust the range in this VLOOKUP to include more of a range sometimes my range is only 1000 rows and sometimes it is over 100000 rows.
right now it is only reading 5281 rows how can I adjust this?

"=VLOOKUP(LOG!RC[-10],PASON!R2C3:R5281C14,9)"

[TMS]

Dim lLR As Long
lLR = Range("C" & Rows.Count).End(xlUp).Row
"=VLOOKUP(LOG!RC[-10],PASON!R2C3:R" & lLR & "C14,9)"

Regards, TMS

[bdouglas1011]

Would I just try the same thing with

Sub Start()

    Rows("2:2").Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("F2").Select
    Range("F3").Select
    ActiveCell.FormulaR1C1 = "=RC[-1]-0.291666666666667"
    Range("F3").Select
    Selection.AutoFill Destination:=Range("F3:F5264")
    Range("F3:F5264").Select
    

Columns("f:f").Select
    With Selection.Font
        .Color = -16776961
        .TintAndShade = 0
    End With

for when it auto fills the range F3:F5264

How would I change that one

[JOHN H. DAVIS]

Same thing:

Sub Start()
Dim lLR As Long
lLR = Range("C" & Rows.Count).End(xlUp).Row
    Rows("2:2").Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("F2").Select
    Range("F3").Select
    ActiveCell.FormulaR1C1 = "=RC[-1]-0.291666666666667"
    Range("F3").Select
    Selection.AutoFill Destination:=Range("F3:F" & lLR)
    Range("F3:F" & lLR).Select
    

Columns("f:f").Select
    With Selection.Font
        .Color = -16776961
        .TintAndShade = 0
    End With

[bdouglas1011]

Thanks that worked great

[bdouglas1011]

Sorry heres another one this one is for inside the chart data ranges.

ActiveChart.FullSeriesCollection(1).Name = "=BOTTOM!$Y$1"
ActiveChart.FullSeriesCollection(1).XValues = "=BOTTOM!$B$2:$B$2757"
ActiveChart.FullSeriesCollection(1).Values = "=BOTTOM!$Y$2:$Y$2757"

How could this one be changed to show entire range in know this range is over 4000 now

can it be done like the other one? Just the range with the last 2 "xvalues & values"

[TMS]

can it be done like the other one?

Other two, in fact.


Probably worth giving it a try

[bdouglas1011]

      lLR = Range("B" & Rows.Count).End(xlUp).Row

     
    ActiveSheet.Shapes.AddChart2(240, xlXYScatterLines).Select
    
    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.FullSeriesCollection(1).Name = "=BOTTOM!$Y$1"
    ActiveChart.FullSeriesCollection(1).XValues = "=BOTTOM!$B$2:$B$ & 1LR"
    ActiveChart.FullSeriesCollection(1).Values = "=BOTTOM!$Y$2:$Y$ & 1LR"

Didn't work application defined error

[TMS]

Look at where the quotes are in the other two.

[bdouglas1011]

I keep getting expected then I add those and get syntax errors then says the $ is invalid characters ....not having much luck

[TMS]

    lLR = Range("B" & Rows.Count).End(xlUp).Row
     
    ActiveSheet.Shapes.AddChart2(240, xlXYScatterLines).Select
    
    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.FullSeriesCollection(1).Name = "=BOTTOM!$Y$1"
    ActiveChart.FullSeriesCollection(1).XValues = "=BOTTOM!$B$2:$B$" & 1LR
    ActiveChart.FullSeriesCollection(1).Values = "=BOTTOM!$Y$2:$Y$" & 1LR

[bdouglas1011]

Yes that is how I had it and you get a syntax error which highlights the entire first row and both rows are in red.

[TMS]

    lLR = Range("B" & Rows.Count).End(xlUp).Row
     
    ActiveSheet.Shapes.AddChart2(240, xlXYScatterLines).Select
    
    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.FullSeriesCollection(1).Name = "=BOTTOM!$Y$1"
    ActiveChart.FullSeriesCollection(1).XValues = "=BOTTOM!$B$2:$B$" & lLR
    ActiveChart.FullSeriesCollection(1).Values = "=BOTTOM!$Y$2:$Y$" & lLR

lLR is lower case l, upper case L, upper case R ... not numeric one (1), upper case L, upper case R


If you use Dim to define (dimension) your variables you would find that you can't have a variable that starts with a number. And, if you use Option Explicit at the top of your module, you won't be able to use a variable that you haven't defined.

[bdouglas1011]

Thanks I got that to work finally Ill make new post now I'm getting new error "Method 'axes of object' _ chart Failed" If I use my old set for my Range it works fine but now with the set I got to work with your help I get that message.

Should I start new Thread?

[TMS]

Should I start new Thread?

Yes. New question, new thread.

Thanks for the rep.

[bdouglas1011]

I tried to delete reply. All good as of now