Help with Do Loop

  • Thread starter Thread starter japorms
  • Start date Start date
J

japorms

Hello guys and guru's

I need help in creating a macro. I am a newbie and browsing the hel
file but reallly I cant get it.

I need a macro that will put this formula =IF(D8>$D$4,D8-$D$4,0)
at F8 to F300

if it satisfy this condition that will look in column E8 to E300 i
equal to "AVE US" And column C8 to C300 is equal to "1/15/2004"

Its giving me a headache coz it has too many condition for a beginner.
If anybody can help please...
 
How about a For Loop example instead?

Sub ForLoopExample()

Dim tw As Workbook
Dim S1 As Worksheet
Dim i As Integer

Set tw = ThisWorkbook
Set S1 = tw.Sheets("Sheet1")

For i = 8 To 300
If S1.Cells(i, "C").Value = "1/15/2004" And _
S1.Cells(i, "E").Value = "AVE US" Then
S1.Cells(i, "F").Formula = _
"=IF($D$8>$D$4,$D$8-$D$4,0)"
End If
Next i

End Sub

You can substitute the numbers 3, 5, and 6 for "C", "E",
and "F", to get the same result.
 
you could try something like this...

Sub DoLoop()

dim counter1 as integer

counter1 = 8

do until counter1 = 301
if sheet1.cells(counter1,5) = "AVE US" _
and sheet1.cells(counter1,3) = "1/15/2004" then
sheet1.cells(counter1,6).formula = _
"=IF($D$8>$D$4,$D$8-$D$4,0)"
end if
counter1 = counter1+1
loop

End Su
 
Morning,

You don't need a loop, a single statement will do it

range("F8:F300").Formula = "=IF(D8>$D$4,D8-$D$4,0)"

Not sure what the second part meant, but looking at Rick's reply, if he
interprets it correctly, it can be done with

range("F8:F300").Formula = "=IF(AND(C8=DATE(2004,1,15),E8=""AVE
US""),IF(D8>$D$4,D8-$D$4,0),"""")"


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Bob,

You are so correct. I've worked with one engineer, that
did all of his "programming" in Excel with formulas. I
tried to get him to switch to VBA programming, but with
his analytical mind, he was very happy to just stick with
formulas.

By the way, if you get a chance to read this, I want to
thank you so much in helping me develop my VBA programming
with VB6 (last year or so). Over the past year part time,
I've gone a lot further...with other people's help too.
I've got the form to stay on top with an API call. I
developed a lot of neat things here that many others
really don't know about. Again, I can't thank you
enough. Now, I don't have any of my VBA code on my
computer at work anymore...just my compilied executable
VB6 programs that interface with Excel. I'm still making
improvements. It works perfect, for what I need it to do.

Thanks, Rick
 
Hi Rick,

Thanks for the comments.

Most people who enquire on these boards are usually looking for a simple,
immediate solution to their current problem, they take it and then cut and
run (no criticism meant, just an observation). It is not so often that we
get people who are on a long journey of learning and improving. They can
usually be spotted by their frequency of return and the intelligence of
their questions (not just 'How do I get the sheet name in a cell?'), and it
is usually the most interesting and enlightening. It looks as though you are
well advanced on that journey, as we (hopefully) also are.

I like running Excel from VB under automation, although it is more work, and
expediency doesn't always allow that luxury.

Look forward to seeing you here some more.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Back
Top