Column = Left(x, InStr(x, "1") - 1)

[rmfrase]

I understand most of this - but I am slighly stumpped on a minor issue.

I've searched for and found a column header - let's call it "LOAN"
Sheets(MainWkSht).Select
Set r = Cells.Find("LOAN")
If r <> "LOAN" then
goto oops
else goto Foundit
end If

'Now grap the column and cell where the header was found ("B1")
location = Cells((r.Column) - 1).Address(0, 0)

'Now strip of the "1" making the result Column ("B")
Location2 = Left(location, InStr(location, "1") - 1)

My question is this. Lets say I want to do something to what ever column to the left or right of where this "LOAN" was found.

How can I manipulate the formula to add or subtract columns from the formula:

Cells(2,location).Select

lets say I want to Identify the 5th column to the right of the string: Location


suggestions?

Thanks.

 

[Jonmo1]

Cells(2,location + 5).Select

 

[rmfrase]

I tried that - but I get a "Type Mismatch" error as Location is a String

 

[Jonmo1]

Range(location).Offset(0,5).Select

 

[Zack Barresse]

Because "location" is a string, and the Cells() function is taking a numeric. Here is some code to chew...

Dim ws as worksheet, r as range
set ws = Sheets(MainWkSht)
Set r = ws.rows(1).Find(what:="LOAN", lookat:=xlwhole, matchcase:=true)
on error resume next '***


'compare these two lines
ws.cells(2, r.column + 5).select
r.offset(1, 0).select

'check out these two lines in your Immediate window (Ctrl + G in the VBE)
debug.print ws.cells(2, r.column + 5).address
debug.print r.offset(1, 0).address

*** Note that this is used in case you enter an erroneous column number. Lets say you found your "LOAN" value in B1 and you put "r.column - 5". That would end up with a non-existent column, hence an error would be produced.

HTH

 

[rmfrase]

Excel-lent - Thank you all for your assistance.

 

[xenou]

I am rather confused...what are you trying to do with these locations, columns, etc.?

Notes:
1)

Set r = Cells.Find("LOAN")
If r <> "LOAN" then

I believe that r is a range, not a string. So r will always be <> "Loan" - if it doesn't error out. I think you really want to test for IS NOTHING -- because if its found, its a range reference (a cell, with the contents "LOAN") and if its not found its Nothing. Also, r is going to be the cell you want, and you already have its column (r.Column).

2)

'Now strip of the "1" making the result Column ("B")
Location2 = Left(location, InStr(location, "1") - 1)

As above, you have the column already (r.Column). Yes, its an integer-2-but this is easy enough to work with in VBA.

3)
To get right or left use OFFSET of a range object:
r.Offset(0,-1) is one cell to the left of r. r.Offset(0,1) is one cell to the right.

4)
or use the cells to enter the address:
Cells(1,r.column - 1) references row 1, one column to the right of r.

5)
Note that offset and cells in VBA are Row-Column, not Column-Row (meaning the order of the arguments - Cells(2,1) is 2nd row, first column, or Cell A2). However, if you use offset with a range (as in # 3 above) then you don't need to know the letter.


Hope this helps.

Also some code to play with (I see a another post has arrived while I was working so this may be some redundancy now):

Sub test()
Dim ws As Worksheet, r As Range

Set ws = Sheets("mySheet")
Set r = ws.Rows(1).Find(what:="LOAN", lookat:=xlWhole, MatchCase:=True)
If r Is Nothing Then
    GoTo oops
Else
    MsgBox ("the cell to the left is " & r.Offset(0, -1).Address)
End If

Exit Sub
oops:
MsgBox ("no cell found")
End Sub

 

[Zack Barresse]

@Alexander Barnes:

I believe that r is a range, not a string. So r will always be <> "Loan" - if it doesn't error out. I think you really want to test for IS NOTHING -- because if its found, its a range reference (a cell, with the contents "LOAN") and if its not found its Nothing. Also, r is going to be the cell you want, and you already have its column (r.Column).

You are correct, except that ..

r <> "LOAN"

.. will not error out. Excel [VBA] assumes Value is meant, as that is the default Let property for that object, although ".Value" should be added. Assumptions are generally not a good thing to do in VBA - or any coding for that matter. I agree though, testing for Nothing is much better.

 

[xenou]

Thanks...sound advice.
Cheers.