Find in a List and move to the right?

Sam40mUK

Board Regular
Joined
Mar 18, 2002
Messages
95
Evening All,
I am looking to find a Value in a list and then move along the row, say 5 cells to the right and paste a value into that cell.
I have looked at a few ways of going about this but is there a 'smarter' way than considering Looping through the list of Values?
Any comments, well most(!), appreciated.
Cheers
S
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
If you're using VBA to do this, have a look at the "Find" function in VBA help. There's a reasonable example there. It's definitely faster than looping through cells. (combine this with "Offset" and you'll be good to go)
 
Upvote 0
Hi Mark,
Thank you for your swift response.
I have had a look at the 'Find Method' Example in VBA Help and have, once again, discovered why I deal with Accounts rather than VBA programming!
Do you happen to know of any other source of relatively easy examples and explanation so I may try to get my head around the Find Method?
Thanks once again
S
This message was edited by Sam40mUK on 2002-04-18 15:42
 
Upvote 0
Yeah, I said that the help file example was reasonable. I don't like it myself, but it's usually a decent place to start. I've knocked up an example for you and I hope it makes enough sense to get you started. I started a new workbook and on Sheet1 I put in the values 1 to 15 in column A starting in A1. I then made this code to find the value "10" and put a message in a box 5 columns to the right.

<pre>
'Declare variables
Dim oTargetRange As Range 'This will be the range we're searching.
Dim sTargetText As String 'This will be text that we're trying to find
Dim oFoundRange As Range 'This will be the range (cell) that contains the "sTargetText"
Dim iColumnOffset As Integer 'This will be the number of columns we want to offset by
Dim sColumnOffsetText As String 'This is the text you want to put in the offset column if the target text is found

'Initialise variables
Set oTargetRange = Sheets("Sheet1").UsedRange.Columns("A")
sTargetText = "10"
iColumnOffset = 5
sColumnOffsetText = "Very Long Variable Name"

'Begin Error trapping (for when we screw up)
On Error GoTo ERR_NOT_FOUND

Set oFoundRange = oTargetRange.Find(What:=sTargetText, LookIn:=xlValues, lookat:=xlWhole)

MsgBox "The text " & Chr(34) & sTargetText & Chr(34) & "was found in " & oFoundRange.Address & "."
oFoundRange.Offset(0, iColumnOffset).Value = sColumnOffsetText


Exit Sub
ERR_NOT_FOUND:
MsgBox "The text " & Chr(34) & sTargetText & Chr(34) & " could not be found in the specified range.", vbInformation, "Not Found"

End Sub</pre>

It shouldn't take too much for you to adapt it to your specific needs. (I hope).

HTH
 
Upvote 0
Firstly thanks Mark,
I appreciate your time in sorting out a further example for me.
I did spend some time looking at VBA Help and found it for a novice rather .. there you go .. take it or leave it!
I am sure that others on this board will agree that a well explained example with the ability to ask, what maybe considered 'silly' questions is an immensely useful way of trying to understand Excel and glimpsing the use of VBA in the 'real world'.
Thanks one again
S
 
Upvote 0
No problems at all. I know that some parts of the help file are better than others and if it was perfect then this board wouldn't exist.

Also, I wasn't sure of your expertise and usually I don't do lazy answers, but I think I had one eye on the golf course yesterday. (additionally, we have all went through what you're going through)
 
Upvote 0
I really liked your example, the sTargettext you set is for a set expression, if I wanted the sTargetText to be based off a cell in another worksheet, how would that look?

sTargetText = worksheets("SheetA").[J4].Value? Range? or something like that?
 
Upvote 0
On 2002-04-19 14:22, heather wrote:
I really liked your example, the sTargettext you set is for a set expression, if I wanted the sTargetText to be based off a cell in another worksheet, how would that look?

sTargetText = worksheets("SheetA").[J4].Value? Range? or something like that?

Heather,

I just saw your post today, you were right first time, but you may have figred that out by now:

<pre>
sTargetText = worksheets("SheetA").[J4].Value</pre>

Although personally, I don't really like using e.g. [J4] for ranges. I'm too used to using "Range" like this:

<pre>
sTargetText = worksheets("SheetA").Range("J4").Value</pre>

...but I have used the []'s in posts here because it's just too damned tempting to use it in a small snippet of code.
 
Upvote 0

Forum statistics

Threads
1,213,535
Messages
6,114,198
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top