Find alpha numeric numberfrom list and then offset ? number of columns

[farmerscott]

Hi Everybody,

I have a simple problem but it exceeds my VBA skills.

I have a list of alpha numeric numbers, such as those below

SP2/1
SP2/2
.....
SP2/100
....
SP2/1000
Sometimes to the left of the "/", changes but not often so it is not an issue at the moment. I am interested in the numbers to the right 1-1000.

As part of data entry, the numbers 1-1000 come to me in a random sequence, and hence if done mannually a lot of scrolling within the worksheet. I want a quick and easy code that will find the relavent number (within the alpha numeric number) and then offset 1 column to the right so I can enter the data. Sometimes there might be more than 1 piece of information so we might need to offset by 2, 3 or 4 columns.


I am happy to use either an input box/s or use cells in row 1 to input values.


The following code is from a recorded macro. It does what I want, except I want "85" as a cell reference so it dynamically changes when data is entered in say "B1".

Cells.Find(What:="85", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False).Activate

ActiveCell.Offset(0, 1).Activate

with thanks

FarmerScott

 

[JLGWhiz]

If you want to use cell B1 as an input range then the code below should work.

Set myCel = Cells.Find(What:=Range("B1").Value, After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False)
If Not myCel Is Nothing Then
        myCel.Offset(0, 1).Activate
End If

 

[farmerscott]

Hello,

thanks for the code. It works!!!

When I was developing the code (see OP), I could not get the find function to accept '=Range("B1")'. That whole section of code went red. It looks like the Set myCell= makes all the difference.

cheers

 

[JLGWhiz]

Hello,

thanks for the code. It works!!!

When I was developing the code (see OP), I could not get the find function to accept '=Range("B1")'. That whole section of code went red. It looks like the Set myCell= makes all the difference.

cheers

Glad it worked for you,
Regards, JLG

 

[farmerscott]

Hi JLG,

have been looking at the practicallities of the data inputting. With 1000 rows i will be doing a bit of scrolling back to the top for the next entry. Would it be better if we used filter then offset cell rather than find then offset.

Then I would not be scrolling back to the top all the time.

cheers

FarmerScott