Can we use offset using column name instead of number?

[NDMDRB]

Hi,

I'm wondering if there is a way to use column name instead of column number with offset function

Let's say we have the following sheet

	A​	B​	c​
1	code​	Plate​	Type​
2	D1	123	Dozer
3	E1	456	Excavator

Dim ws as Worksheet
Set ws = Sheet5

If Me.Search_Code_BT.Object = True Then
    Set f = ws.Range("C:C").Find(what:=Me.Search_CMB.value, LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False)
    If f Is Nothing Then Exit Sub
    Me.Plate_TXT = f.Offset(0, 1)
    Me.Type_CMB = f.Offset(0, 2)
    '..... the same for other textboxes
ElseIf Me.Search_Plate_BT.Object = True Then
    Set f = ws.Range("D:D").Find(what:=Me.Search_CMB.value, LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False)
    If f Is Nothing Then Exit Sub
    Me.Code_TXT = f.Offset(0, -1)
    Me.Type_CMB = f.Offset(0, 1)
    '..... the same for other textboxes
End if
End If

In this case I have to use different Offset for each condition

I'm wondering if there is a shortcut to use the Offset one time after the If Else statement as the example below

If Me.Search_Code_BT.Object = True Then
    Set f = ws.Range("C:C").Find(what:=Me.Search_CMB.value, LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False)
    If f Is Nothing Then Exit Sub
ElseIf Me.Search_Plate_BT.Object = True Then
    Set f = ws.Range("D:D").Find(what:=Me.Search_CMB.value, LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False)
End If

    Me.Plate_TXT = f.Offset(0, B)
    Me.Type_CMB = f.Offset(0, C)
    '..... the same for other textboxes

I know that the second code doesn't work, but I'm not sure if it's possible in another way
Or if you have another way to get what I need using another function, it will be appreciated to learn it

Many thanks in advanced

 

[offthelip]

You can convert a single letter to the column number using this code:

Sub test()
ii = "A"
yy = Asc(ii) - 64
MsgBox yy



End Sub

so you could try:

Me.Plate_TXT = f.Offset(0, Asc("B") - 64)

 

[Tetra201]

Maybe

    Me.Plate_TXT = Application.Intersect(f.EntireRow, Range("B:B"))
    Me.Type_CMB = Application.Intersect(f.EntireRow, Range("C:C"))

 

[NDMDRB]

You can convert a single letter to the column number using this code:

Sub test()
ii = "A"
yy = Asc(ii) - 64
MsgBox yy



End Sub

so you could try:

Me.Plate_TXT = f.Offset(0, Asc("B") - 64)

Thank you offthelip,

It works but the same I have to use this for Code search

Me.Plate_TXT = f.Offset(0, Asc("B") - 67)

And this for Plate search

Me.Plate_TXT = f.Offset(0, Asc("B") - 68)

 

[NDMDRB]

Maybe

    Me.Plate_TXT = Application.Intersect(f.EntireRow, Range("B:B"))
    Me.Type_CMB = Application.Intersect(f.EntireRow, Range("C:C"))

Thank you Tetra201,
This works great as I need
Just one small issue and not sure if we can do it
With your code, I have to select the needed sheet (Sheet5) to make it done, so just wondering if you can make it even without selecting this sheet it also works

 

[YasserKhalil]

No worksheet activating is necessary. The code will work without the need to select or activate the worksheet.

 

[NDMDRB]

No worksheet activating is necessary. The code will work without the need to select or activate the worksheet.

If Sheet5 is not active this error happened
Method 'Intersect' of objects '_Application' failed

 

[YasserKhalil]

Maybe because you didn't refer to the worksheet at this part

Range("B:B")

Try refer to the worksheet before the range like that

Sheet1.Range("B:B")

 

[NDMDRB]

Maybe because you didn't refer to the worksheet at this part

Range("B:B")

Try refer to the worksheet before the range like that

Sheet1.Range("B:B")

Yes that's it, thank you

 

[NDMDRB]

Hi again

I'm trying to get comment text from a cell to a textbox as below code, but it didn't work, any advices?

Me.Plate_TXT = Application.Intersect(f.EntireRow, ws.Range("B:B"))

If Not Application.Intersect(f.EntireRow, ws.Range("B:B").Comment.Text) Is Nothing Then
    Me.Attached_TXT = Application.Intersect(f.EntireRow, ws.Range("B:B").Comment.Text)
End If