Repeat statements "x" number of times...

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi All,

I have the following code at the end of a long macro:

Columns("D:D").Find(What:="total").Select
ActiveCell.Offset(0, 4).Range("A1").Select
ActiveCell.FormulaR1C1 = "=Sum(RC[-1]-RC[-2])"
ActiveCell.NumberFormat = "0.00"
ActiveCell.Font.Bold = True
With ActiveCell.Interior
.ColorIndex = 6
End With

What I am doing is when the word "total" appears, it will go to the cell 4
columns to the right, and then do a formula and format the cell.

Question: How can I get this to repeat itself for each time the word
"total" appears. Each different time I run this macro, I may have anywhere
from 1 to 100 totals in the respective worksheet. I think that I need to
either loop, or do a for..next statement, but I don't know how to get the
syntax right, or which would work better.

Any help would be greatly appreciated...thanks in advance...

Sara
 
No need to repeat the statements, just need to find all the values first.

HTH,
Bernie
MS Excel MVP

Sub FindValues()
Dim c As Range
Dim d As Range
Dim firstAddress As String

'First, find all the cells
With Cells

Set c = .Find("Total", LookIn:=xlValues, lookAt:=xlWhole)

If Not c Is Nothing Then
Set d = c
firstAddress = c.Address
Else:
MsgBox "Not Found"
End
End If

Set c = .FindNext(c)
If Not c Is Nothing And c.Address <> firstAddress Then
Do
Set d = Union(d, c)
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
End With

' Then do what you want with the offset of
' all the cells that have been found:
With d.Offset(0, 4)
.FormulaR1C1 = "=Sum(RC[-1]-RC[-2])"
.NumberFormat = "0.00"
.Font.Bold = True
With .Interior
.ColorIndex = 6
End With
End With

End Sub
 
Bernie,

This isn't working...it is popping up the box and saying not found....

Bernie Deitrick said:
No need to repeat the statements, just need to find all the values first.

HTH,
Bernie
MS Excel MVP

Sub FindValues()
Dim c As Range
Dim d As Range
Dim firstAddress As String

'First, find all the cells
With Cells

Set c = .Find("Total", LookIn:=xlValues, lookAt:=xlWhole)

If Not c Is Nothing Then
Set d = c
firstAddress = c.Address
Else:
MsgBox "Not Found"
End
End If

Set c = .FindNext(c)
If Not c Is Nothing And c.Address <> firstAddress Then
Do
Set d = Union(d, c)
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
End With

' Then do what you want with the offset of
' all the cells that have been found:
With d.Offset(0, 4)
.FormulaR1C1 = "=Sum(RC[-1]-RC[-2])"
.NumberFormat = "0.00"
.Font.Bold = True
With .Interior
.ColorIndex = 6
End With
End With

End Sub


Frantic Excel-er said:
Hi All,

I have the following code at the end of a long macro:

Columns("D:D").Find(What:="total").Select
ActiveCell.Offset(0, 4).Range("A1").Select
ActiveCell.FormulaR1C1 = "=Sum(RC[-1]-RC[-2])"
ActiveCell.NumberFormat = "0.00"
ActiveCell.Font.Bold = True
With ActiveCell.Interior
.ColorIndex = 6
End With

What I am doing is when the word "total" appears, it will go to the cell 4
columns to the right, and then do a formula and format the cell.

Question: How can I get this to repeat itself for each time the word
"total" appears. Each different time I run this macro, I may have anywhere
from 1 to 100 totals in the respective worksheet. I think that I need to
either loop, or do a for..next statement, but I don't know how to get the
syntax right, or which would work better.

Any help would be greatly appreciated...thanks in advance...

Sara
Click to expand...
Click to expand...
 
Bernie...

I think I figured it out....I changed the .find....xlwhole to
..find.....xlpart...because the totals have fiscal years in front of
them...i.e. F05 total...


The only question I have now is I only want it to look in column D for this,
not the whole document because there might be the word "total in other
columns....

Frantic Excel-er said:
Bernie,

This isn't working...it is popping up the box and saying not found....

Bernie Deitrick said:
No need to repeat the statements, just need to find all the values first.

HTH,
Bernie
MS Excel MVP

Sub FindValues()
Dim c As Range
Dim d As Range
Dim firstAddress As String

'First, find all the cells
With Cells

Set c = .Find("Total", LookIn:=xlValues, lookAt:=xlWhole)

If Not c Is Nothing Then
Set d = c
firstAddress = c.Address
Else:
MsgBox "Not Found"
End
End If

Set c = .FindNext(c)
If Not c Is Nothing And c.Address <> firstAddress Then
Do
Set d = Union(d, c)
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
End With

' Then do what you want with the offset of
' all the cells that have been found:
With d.Offset(0, 4)
.FormulaR1C1 = "=Sum(RC[-1]-RC[-2])"
.NumberFormat = "0.00"
.Font.Bold = True
With .Interior
.ColorIndex = 6
End With
End With

End Sub


Frantic Excel-er said:
Hi All,

I have the following code at the end of a long macro:

Columns("D:D").Find(What:="total").Select
ActiveCell.Offset(0, 4).Range("A1").Select
ActiveCell.FormulaR1C1 = "=Sum(RC[-1]-RC[-2])"
ActiveCell.NumberFormat = "0.00"
ActiveCell.Font.Bold = True
With ActiveCell.Interior
.ColorIndex = 6
End With

What I am doing is when the word "total" appears, it will go to the cell 4
columns to the right, and then do a formula and format the cell.

Question: How can I get this to repeat itself for each time the word
"total" appears. Each different time I run this macro, I may have anywhere
from 1 to 100 totals in the respective worksheet. I think that I need to
either loop, or do a for..next statement, but I don't know how to get the
syntax right, or which would work better.

Any help would be greatly appreciated...thanks in advance...

Sara
Click to expand...
Click to expand...
Click to expand...
 
Yikes! How did that happen?

With Rand("D:D")

should have been

With Range("D:D")

HTH,
Bernie
MS Excel MVP
 
Hi Bernie,

Thanks so much for all the help....The code works great....I can't stop
re-running it just to see it work.....This is very helpful....and also,
thanks for the tip on finding the word first, and then executing the
formating steps.....I would have never even known you can do that.....

One more question........what would be a good direction to go in to learn
more about visual basic?????
 
Lurk here and read the posts and answers, and read any book in the VBA Power
Programming series by John Walkenbach.

HTH,
Bernie
MS Excel MVP
 
Bernie,

I have some more formatting issues now that pertain to the code you wrote me
in this thread.

I now need to find in column H...all the cells that have a formula response
of #N/A, or "India Only", and I need to highlight those in red. I tried to
copy this code that formatted the other column for me, but it doesn't work -
I get a duplicate compile error. Any suggestions on how I can get these
responses highlighted????

Thanks.

Sara
 
I tried to use conditional formatting, but it doesn't seem to know that I
want it to look at a formula result....Also, this is the last bit of
formatting that I need to do in order to finish a massive macro that I wrote.
I would like for it to be included in the macro. I have recorded a macro
while doing conditional format, and it looks like this:
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
Formula1:="=$O$146"
Selection.FormatConditions(1).Interior.ColorIndex = 3

the formulas in column H are vlookups with code that looks like this:
With ActiveCell
RowCount = .Offset(0, -1).End(xlDown).Row - .Offset(0, -1).Row + 1
.FormulaR1C1 = _

"=IF(RC[-4]=""C"",VLOOKUP(RC[-3],USB,2,FALSE),VLOOKUP(RC[-3],FAS,2,FALSE))"
.AutoFill .Resize(RowCount)
End With

Not sure how to combine the two.
 
Okay.....I got it to format the "India Only" because that is an actual return
from my vlookup...The "#N/A" I am having trouble with because that is
referring to the account number not being on my vlookup table. I have tried
that several ways, and can't seem to figure that one out....

here is the code I have for the formatting of India only
Range("H:H").Select
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
Formula1:="=""India Only"""
Selection.FormatConditions(1).Interior.ColorIndex = 7
 
Bernie.....help......please......I am really trying hard......I have been at
it for almost 4 hours........going crazy....

Can't figure out how to conditional format when the formula result is
#N/A.....
 
Sorry, Frantic - I went on a short vacation...


Range("H:H").Select
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
Formula1:="=""India Only"""
Selection.FormatConditions(1).Interior.ColorIndex = 7
Selection.FormatConditions.Add Type:=xlExpression,
Formula1:="=ISERROR(H1)"
Selection.FormatConditions(2).Interior.ColorIndex = 7


HTH,
Bernie
MS Excel MVP
 
Thanks Bernie...

I finally figured it out the other day....

I felt so dumb....the whole time, the formatting wasn't working because I
wasn't in the first cell of the row I was trying to fix....

I do have another question that would be great if you can help with...it is
a separate thread "Macro - message box if find nothing"....Jim Rech was
helping, but he decided I should be able to figure it out on my own, and I am
stuck...If you don't mind reading thru, I would appreciate some input...

Thanks again...

Sara
 
Back
Top