How To Iterate a range of cells?

[StarGazerNC]

I need to make a macro that does the following things:
1. Search for a specific string in one column;
2. If the string is found, add the value in same row, but in differen
column to a variable;
3. Display that variable in a specific cell;

The points I can't seem to be managing to do are 1. and maybe 2.

Is there anyone who could help me?

Thanks in advance,

Nun

 

[pikus]

lrow = Sheet1.UsedRange.Row + Sheet1.UsedRange.Rows.Count
col = column number you are searching
n = Destination cell Row
p = Destination Cell Column
For x = 1 To lrow
If Cells(x, col).Value = "Search String" Then
varNum = Cells(x, col).Value + Cells(x, col + 1).Value
Cells(n, p).Value = varNum
End If
Next

 

[mangesh_yadav]

just use the VLOOKUP function as follows

VariableToAdd
Application.WorksheetFunction.VLookup(str,range("A1:B10"),2)

where str is your string and column A1 to A10 contains all the string
and cloumn B1 to B10 contain the numbers.

- Manges

 

[StarGazerNC]

Thanks, I'll try that

 

[pikus]

Careful though. I blieve that will only work if you know for a fact yo
will only get one match for your lookup string

 

[StarGazerNC]

Yeah...I've just noticed that. The VLookUp aproach is nice, but onl
accounts for ONE occurence of the string...

This is to automate production of Totals, from Sub-Total Cells.

The Sub-Totals are made by =Sum(). Now, I need to automate th
production of Totals, which will be the sum of all Sub-Totals, which
never lknow how many they are, reason for which I need to iterate th
cells looking for the string "Sub-Total".

Was I clear? If not please tell me. Sometimes I do have the tendency t
maku simple things, complicated ones. :s

StargazerN