I am having an issue with error

Question

I am new to Excel VBA and I am working on a project which prints a report. I am getting an error when I run this excel VBA code. The error is at the "Columnoffset" line any help is appreciated. Thanks

Workbooks(MainWbName).Worksheets("DATA").Activate

rowoffset = DataRow - 1

' Activate WORKBOOK
Workbooks(MainWbName).Activate

Dim ColumnRangeName As String
Dim ColumnDefRange As Object
tabletouse = "ColumnDefinitions"
Sheettouse = "ColumnDefs"
Set ColumnDefRange = Worksheets(Sheettouse).Range(tabletouse)

If FieldTitle = " " Then Exit For

ColumnRangeName = WorksheetFunction.VLookup(FieldTitle, ColumnDefRange, 2, False)   

Columnoffset = Range(ColumnRangeName).Value   ' <-- Error

Workbooks(CSVFileName).Activate

Hi, ColumnRangeName is "B1InletO2" which is I am looking for but the issue is getting its value for Columnoffset = Range(ColumnRangeName).Value — Aman_XP, Mar 04 '20 at 19:36
No, It is a variable name. But the value of Columnoffset is returning Empty. I don't know why? Can you help me? — Aman_XP, Mar 04 '20 at 19:56
Your question is unclear: is `"B1InletO2"` a named range in your workbook? If it isn't, you can't do `Range("B1InletO2")`... — BigBen, Mar 04 '20 at 20:04

score 0 · Answer 1 · answered Mar 04 '20 at 16:24

0

Whenever you are working with VLookup, it is best to handle the error in case the lookup text is not found.

'
'~~> Rest of your code
'

Dim errorNumber As Long

On Error Resume Next
ColumnRangeName = WorksheetFunction.VLookup(FieldTitle, ColumnDefRange, 2, False)
errorNumber = Err.Number
On Error GoTo 0

If errorNumber <> 0 Then
    MsgBox FieldTitle & " not found"
Else
    Columnoffset = Range(ColumnRangeName).Value

    '
    '~~> Rest of your code
    '
End If

TIP: Also avoid the use of .Activate. You may want to see How to avoid using Select in Excel VBA

answered Mar 04 '20 at 16:24

Siddharth Rout

147,039
17
206
250

I am getting the same error even after the error handling still pointing at the Columnoffset = Range(ColumnRangeName).Value – Aman_XP Mar 04 '20 at 17:02
Then it means that the value of `ColumnRangeName` is not being resolved to a range address. If you feel that the name is correct then check for leading or trailing spaces. perhaps `Range(Trim(ColumnRangeName)).Value` – Siddharth Rout Mar 04 '20 at 17:05
It is returning Empty. Do you know why? – Aman_XP Mar 04 '20 at 19:52
What is the value of `FieldTitle` – Siddharth Rout Mar 05 '20 at 04:13

I am having an issue with error

1 Answers1