PDA

View Full Version : Solved: Macro Help



shuco
12-11-2008, 06:43 PM
I am trying to make macro that looks at 3 cells on each row , determines what they are and fills a cell with a value until there are 4 continuious blank spaces.

ActiveCell would be from E2 until the end of the document.

In my code I will have values of HEALTH 1 , HEALTH 2 and HEALTH 3 G2 until the end of the document

N2 will have SN, SS, SD and FM until the end of the document.

This is what I have so far:



Sub macro()

Range("e2").Select

Do
If (ActiveCell = "00170016") Then
If (ActiveCell.Select.Offset(0, 9) = "SN") Then
Sheet1.Range("h2") = "$301"
End If
If (ActiveCell.Select.Offset(0, 9) = "SS") Then
Sheet1.Range(ActiveCell.Offset(0, 3)) = "$734.39"
End If
If (ActiveCell.Select.Offset(0, 9) = "SD") Then
Sheet1.Range(ActiveCell.Offset(0, 3)) = "$734.39"
End If
If (ActiveCell.Offset(0, 9) = "FM") Then
Sheet1.Range(ActiveCell.Offset(0, 3)) = "$1044.87"
End If
End If
If (ActiveCell = "00170017") Then
If (ActiveCell.Offset(0, 2) = "HEALTH 1") Then
If (ActiveCell.Offset(0, 9) = "SN") Then
ActiveCell.Offset(0, 3) = "$267.97"
End If
If (ActiveCell.Offset(0, 2) = "HEALTH 2") Then
If (ActiveCell.Offset(0, 9) = "SS") Then
ActiveCell.Offset(0, 3) = "$658.26"
End If
If (ActiveCell.Offset(0, 9) = "SD") Then
ActiveCell.Offset(0, 3) = "$658.26"
End If
If (ActiveCell.Offset(0, 9) = "FM") Then
ActiveCell.Offset(0, 3) = "$937.9"
End If
End If
If (ActiveCell.Offset(0, 2) = "HEALTH 3") Then
If (ActiveCell.Offset(0, 9) = "SN") Then
ActiveCell.Offset(0, 3) = "$217.6"
End If
If (ActiveCell.Offset(0, 9) = "SS") Then
ActiveCell.Offset(0, 3) = "$537.29"
End If
If (ActiveCell.Offset(0, 9) = "SD") Then
ActiveCell.Offset(0, 3) = "$537.29"
End If
If (ActiveCell.Offset(0, 9) = "FM") Then
ActiveCell.Offset(0, 3) = "$761.6"
End If
End If
End If
End If

ActiveCell.Offset(1, 0).Select
Loop Until IsEmpty(ActiveCell.Offset(1, 0))

End Sub


I am pretty new to VBA, but I have coded in php and c#

That said I am not sure the correct way to check a field by an offset of (0,2) etc...

Any Help would be GREAT!

nst1107
12-11-2008, 08:51 PM
If you posted an example workbook, it would help a lot. However, I'll try to guess what you're getting at here...
Sub macro()
Sheet1.Range("E2").Select 'Is this the sheet this range is supposed to refer to?
With ActiveCell
Do Until IsEmpty(ActiveCell)
If .Value = "00170016" Then
If .Offset(, 9) = "SN" Then .Offset(, 3) = "$301"
If .Offset(, 9) = "SS" Then .Offset(, 3) = "$734.39"
If .Offset(, 9) = "SD" Then .Offset(, 3) = "$734.39"
If .Offset(, 9) = "FM" Then .Offset(, 3) = "$1044.87"
End If
If .Value = "00170017" Then
If .Offset(, 2) = "HEALTH 1" Then
If .Offset(, 9) = "SN" Then .Offset(, 3) = "$267.97"
If .Offset(, 2) = "HEALTH 2" Then
If .Offset(, 9) = "SS" Then .Offset(, 3) = "$658.26"
If .Offset(, 9) = "SD" Then .Offset(, 3) = "$658.26"
If .Offset(, 9) = "FM" Then .Offset(, 3) = "$937.9"
End If
If .Offset(, 2) = "HEALTH 3" Then
If .Offset(, 9) = "SN" Then .Offset(, 3) = "$217.6"
If .Offset(, 9) = "SS" Then .Offset(, 3) = "$537.29"
If .Offset(, 9) = "SD" Then .Offset(, 3) = "$537.29"
If .Offset(, 9) = "FM" Then .Offset(, 3) = "$761.6"
End If
End If
End If
.Offset(1).Select
Loop
End With
End Sub

shuco
12-11-2008, 09:27 PM
If I step through it it doesn't select the cell for :

If .Offset(, 2) = "HEALTH 1" Then

nst1107
12-11-2008, 09:36 PM
You can reference and use cells in code without selecting them on the sheet. In fact, you don't have to select the range "E2" at the beginning at all. You could use instead:


Sub macro()
Dim i as Long
i = 2

Do Until IsEmpty(Sheet1.Range("E" & i))
With Sheet1.Range("E" & i) 'Is this the sheet this range is supposed to refer to?
If .Value = "00170016" Then
If .Offset(, 9) = "SN" Then .Offset(, 3) = "$301"
If .Offset(, 9) = "SS" Then .Offset(, 3) = "$734.39"
If .Offset(, 9) = "SD" Then .Offset(, 3) = "$734.39"
If .Offset(, 9) = "FM" Then .Offset(, 3) = "$1044.87"
End If
If .Value = "00170017" Then
If .Offset(, 2) = "HEALTH 1" Then
If .Offset(, 9) = "SN" Then .Offset(, 3) = "$267.97"
If .Offset(, 2) = "HEALTH 2" Then
If .Offset(, 9) = "SS" Then .Offset(, 3) = "$658.26"
If .Offset(, 9) = "SD" Then .Offset(, 3) = "$658.26"
If .Offset(, 9) = "FM" Then .Offset(, 3) = "$937.9"
End If
If .Offset(, 2) = "HEALTH 3" Then
If .Offset(, 9) = "SN" Then .Offset(, 3) = "$217.6"
If .Offset(, 9) = "SS" Then .Offset(, 3) = "$537.29"
If .Offset(, 9) = "SD" Then .Offset(, 3) = "$537.29"
If .Offset(, 9) = "FM" Then .Offset(, 3) = "$761.6"
End If
End If
End If
End With
i = i + 1
Loop
End Sub

If you run the code, does it do what you want?

shuco
12-11-2008, 09:56 PM
When I run it, it gets to same point :



If .Offset(, 2) = "HEALTH 1" Then


Then falls out of th if statement. I have double checked 2 columns over to the right and it does say HEALTH 1.

So I am not sure what is going on.

shuco
12-11-2008, 09:57 PM
Also the counter is not pushing it down a row eithier for some reason

nst1107
12-11-2008, 11:23 PM
It works fine for me. One thing: it didn't work if the text in the sheet isn't in all caps like you have it in the code. If that's the problem, place Option Compare Text at the top of the module, above all other code. It should work fine then.

shuco
12-15-2008, 01:38 AM
No it won't work.

It still skips over the HEALTH 1 line.

I have attached a test book.

Bob Phillips
12-15-2008, 02:24 AM
Sub macro()
Dim LastRow As Long
Dim i As Long

With Sheet1

LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

For i = 2 To LastRow

With .Cells(i, "E")
If Trim(.Value) = "00170016" Then
If .Offset(, 9).Value = "SN" Then .Offset(, 3) = "$301"
If .Offset(, 9).Value = "SS" Then .Offset(, 3) = "$734.39"
If .Offset(, 9).Value = "SD" Then .Offset(, 3) = "$734.39"
If .Offset(, 9).Value = "FM" Then .Offset(, 3) = "$1044.87"
ElseIf Trim(.Value) = "00170017" Then
If Trim(.Offset(, 2).Value) = "HEALTH 1" Then
If .Offset(, 9).Value = "SN" Then .Offset(, 3) = "$267.97"
ElseIf Trim(.Offset(, 2).Value) = "HEALTH 2" Then
If .Offset(, 9).Value = "SS" Then .Offset(, 3) = "$658.26"
If .Offset(, 9).Value = "SD" Then .Offset(, 3) = "$658.26"
If .Offset(, 9).Value = "FM" Then .Offset(, 3) = "$937.9"
ElseIf Trim(.Offset(, 2).Value) = "HEALTH 3" Then
If .Offset(, 9).Value = "SN" Then .Offset(, 3) = "$217.6"
If .Offset(, 9).Value = "SS" Then .Offset(, 3) = "$537.29"
If .Offset(, 9).Value = "SD" Then .Offset(, 3) = "$537.29"
If .Offset(, 9).Value = "FM" Then .Offset(, 3) = "$761.6"
End If
End If
End With
Next i
End With
End Sub

georgiboy
12-15-2008, 02:53 AM
Bob you beat me to it :). Just to clarify a few things for Nate and Shuco the cells with the numbers in i.e. "00170017" have got extra spaces in so they appear like this "00170017 " in the formula bar so the code will miss these unless you enter these spaces in the code or trim them off by using trim in the code. This is the same for your "HEALTH 1" cells they appear like this "HEALTH 1 ", not all of your cells have these extra spaces so its best to use trim anyway.

Hope this info helps

shuco
12-15-2008, 08:30 AM
It worked!

Thanks for the help everyone!

So it just came down to having to .trim() the whitespace?!?

nst1107
12-15-2008, 09:12 AM
White spaces... Would never have thought that was the problem.

Bob Phillips
12-15-2008, 09:24 AM
No, it also came down to getting you code for calculating the last row right.