Help with Setting Dim as Range

[nwd9s]

Hi All-

I am looking for your advice on why this setting vba code below is not working. The error is on the last line where I am trying to set 'Custom' as a range. Please note I have omitted non-relevant vba code:

Sheets("Future NBV").Activate
    Dim NBVColumn As Long
        NBVColumn = Cells(1, Columns.Count).End(xlToLeft).Column
    Dim NBVRow As Long
        NBVRow = Cells(Rows.Count, "A").End(xlUp).Row
Sheets("Macro Workspace").Activate
    Dim Custom As Range
        Custom = Range(Cells(NBVRow, NBVColumn))

Again, many thanks for your prompt guidance!

Regards,
nwd9s

 

[VoG]

Should it be?

Set Custom = Range(Cells(1, 1), Cells(NBVRow, NBVColumn))

 

[nwd9s]

Thanks for the response. I am only looking for that particular cell, as it is at the end of data set that can dynamic rows and columns.

So the Dim Custom range should only be something like AF200

Thanks,
nwd9s

 

[VoG]

In that case try

Set Custom = Cells(NBVRow, NBVColumn)

 

[nwd9s]

Thanks for the help, I have that line working but now have issues with my formula below. I am trying to use Custom to reference the lookup area in the index formula. Would you have an idea how to correct the syntax or a better way to do this?

Just note it is the tab 'Future NBV' where the columns or rows could change based on it being updated each month. Also, I cut the formula short as the other part is not relevant and wanted to focus on this issue.

Dim Custom As Range
Set Custom = Cells(NBVRow, NBVColumn)
Range("Q5:Q" & WorkSpace).Formula = "=IF(ISERROR(INDEX(INDEX('Future NBV'!$B$3:$" & Custom & ",MATCH(B5,'Future NBV'!$A$3:$A$" & NBVRow & ",0),0),MATCH(TRUE,INDEX(INDEX('Future NBV'!$B$3:$" & Custom & ",MATCH(B5,'Future NBV'!$A$3:$A$" & NBVRow & ",0),0)>0,0)CODE]
 
Thanks,
nwd9s

 

[nwd9s]

Sorry, proper code below:

Dim Custom As Range
        Set Custom = Cells(NBVRow, NBVColumn)
    Range("Q5:Q" & WorkSpace).Formula = "=IF(ISERROR(INDEX(INDEX('Future NBV'!$B$3:$" & Custom & ",MATCH(B5,'Future NBV'!$A$3:$A$" & NBVRow & ",0),0),MATCH(TRUE,INDEX(INDEX('Future NBV'!$B$3:$" & Custom & ",MATCH(B5,'Future NBV'!$A$3:$A$" & NBVRow & ",0),0)>0,0),0)+1)),0,INDEX(INDEX('Future NBV'!$B$3:$" & Custom & ",MATCH(B5,'Future NBV'!$A$3:$A$" & NBVRow & ",0),0),MATCH(TRUE,INDEX(INDEX('Future NBV'!$B$3:$" & Custom & ",MATCH(B5,'Future NBV'!$A$3:$A$" & NBVRow & ",0),0)>0,0),0))-INDEX(INDEX('Future NBV'!$B$3:$" & Custom & ",MATCH(B5,'Future NBV'!$A$3:$A$" & NBVRow & ",0),0),MATCH(TRUE,INDEX(INDEX('Future NBV'!$B$3:$" & Custom & ",MATCH(B5,'Future NBV'!$A$3:$A$" & NBVRow & ",0),0)>0,0),0)+1))"

 

[VoG]

Sorry I'm not sure what that is meant to do. It looks like you just need the row number (which you already have) rather that the range. Or maybe it should be Custom.Address?

 

[nwd9s]

Hi VoG-

The concept was for this part of the formula:

INDEX('Future NBV'!$B$3:$" & Custom & ",MATCH(B5,'Future NBV'!$A$3:$A$" & NBVRow & ",0)

Basically I have this data table called Future NBV which I update each month and I am trying to match it to a month. I have tested the formula and it is working in Excel, it is just the syntax of how to create it in the macro to auto update the index range where the data is constantly changing:

You see this part: 'Future NBV'!$B$3:$" & Custom & "

I am trying to reference the data tab, but because I update it, it changes each month. One month the range might be B3:AF200 and the next month B3:BA500, ect. I am trying to create a dim that will lookup the last row and column so my index formula will reference the entire data set.

Hopefully this makes sense, please let me know if I can provide any further clarity.

Thanks,
nwd9s

 

[VoG]

In that case I think it should be like this

INDEX('Future NBV'!$B$3:" & Custom.Address

 

[nwd9s]

Thank you for your help VoG, this works great!!