Find in a List and move to the right?

[Sam40mUK]

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

[Mark O'Brien]

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)

[Sam40mUK]

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 ]

[Mark O'Brien]

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.

'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

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

HTH

[Sam40mUK]

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

[Mark O'Brien]

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)

[heather]

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?

[Mark O'Brien]

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:

sTargetText = worksheets("SheetA").[J4].Value

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

sTargetText = worksheets("SheetA").Range("J4").Value

...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.