Find then select to the right
 

Hello,
I want to search in Column A, example "Total", then move
to Column C so that I can calculate percentage. I did the
following which works fine. But this will only work is if
rows don't change, but this will not be the case. So I
want to be able to find Total then move to the right to
col C to calculate.

Sub Test()
'
r = Cells.Find(What:="Total OEM", After:=ActiveCell,
LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByColumns,
SearchDirection:=xlNext, _
MatchCase:=True).Activate
If r = True Then
Range("C44").Select
ActiveCell.FormulaR1C1 = "=RC[2]/RC[1]"
Range("C44").Select
Selection.NumberFormat = "0.00%"
ElseIf r = False Then
'do nothing
End If
Please advise any help would be very appreciated.
Thanks,
juan

Find then select to the right
 

Try this:

For i = 1 To ActiveSheet.UsedRange.Rows.Count
If Cells(i, "A") = "Total OEM" Then Cells(i, "C") = Cells
(i, "A") / Cells(i, "B")
Next i

John
www.SpreadsheetSolutions.com

-----Original Message-----
Hello,
I want to search in Column A, example "Total", then move
to Column C so that I can calculate percentage. I did the
following which works fine. But this will only work is if
rows don't change, but this will not be the case. So I
want to be able to find Total then move to the right to
col C to calculate.

Sub Test()
'
r = Cells.Find(What:="Total OEM",
After:=ActiveCell,
LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByColumns,
SearchDirection:=xlNext, _
MatchCase:=True).Activate
If r = True Then
Range("C44").Select
ActiveCell.FormulaR1C1 = "=RC[2]/RC[1]"
Range("C44").Select
Selection.NumberFormat = "0.00%"
ElseIf r = False Then
'do nothing
End If
Please advise any help would be very appreciated.
Thanks,
juan
.

Find then select to the right
 

Hello John,
Your code didn't quite work, but I was able to figure it
out. here's the complete code I use and works:
Dim i as long
For i= 1To ActiveSheet.UsedRange.Rows.Count
If Cells(i,"A") = "Total" Then
Cells(i, "C").Value = Cells (i, "A") / Cells(i, "B")
Elseif 'do nothing
endif
Next i

Thanks for your help. really appreciated it.

Juan

-----Original Message-----
Try this:

For i = 1 To ActiveSheet.UsedRange.Rows.Count
If Cells(i, "A") = "Total OEM" Then Cells(i, "C") = Cells
(i, "A") / Cells(i, "B")
Next i

John
www.SpreadsheetSolutions.com

-----Original Message-----
Hello,
I want to search in Column A, example "Total", then move
to Column C so that I can calculate percentage. I did
the
following which works fine. But this will only work is
if
rows don't change, but this will not be the case. So I
want to be able to find Total then move to the right to
col C to calculate.

Sub Test()
'
r = Cells.Find(What:="Total OEM",
After:=ActiveCell,
LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByColumns,
SearchDirection:=xlNext, _
MatchCase:=True).Activate
If r = True Then
Range("C44").Select
ActiveCell.FormulaR1C1 = "=RC[2]/RC[1]"
Range("C44").Select
Selection.NumberFormat = "0.00%"
ElseIf r = False Then
'do nothing
End If
Please advise any help would be very appreciated.
Thanks,
juan
.

.