Excel macro programming question

[Anne]

Hello!
I have a worksheet with cols. A, B, C, D, and E. I want to do an if-then
statement in a macro that says, If B has "42" in it (no matter how long the
worksheet is, because they will vary widely), then divide the amount paid (E)
by 2.

B1 will always have Fund as its name; E1 will always have Amount Paid. As
mentioned, the size of the worksheet may vary a lot, so I was thinking I'd
use a range, but I'm not sure how to.

Example:
Columns
A B C D E
Row
1 Dept. Fund Vendor Desc Amount Paid

2 100 42 X Y 100.00

3 100 43 Z A 100.00

4 101 43 M G 100.00

After the code ran, E2 would be 50.00.

I'd like to put this in as a module, to make a little macro button for it.
(I know how to do that.) Any help would be appreciated. --Anne

 

[Lars-Åke Aspelin]

Hello!
I have a worksheet with cols. A, B, C, D, and E. I want to do an if-then
statement in a macro that says, If B has "42" in it (no matter how long the
worksheet is, because they will vary widely), then divide the amount paid (E)
by 2.

B1 will always have Fund as its name; E1 will always have Amount Paid. As
mentioned, the size of the worksheet may vary a lot, so I was thinking I'd
use a range, but I'm not sure how to.

Example:
Columns
A B C D E
Row
1 Dept. Fund Vendor Desc Amount Paid

2 100 42 X Y 100.00

3 100 43 Z A 100.00

4 101 43 M G 100.00

After the code ran, E2 would be 50.00.

I'd like to put this in as a module, to make a little macro button for it.
(I know how to do that.) Any help would be appreciated. --Anne

Try this macro:

Sub anne()
first_row = 2
last_row = Cells(65535, "B").End(xlUp).Row
For r = first_row To last_row
If Cells(r, "B") = 42 Then
Cells(r, "E") = Cells(r, "E") / 2
End If
Next r
End Sub

Hope this helps / Lars-Åke

 

[JLGWhiz]

Press Alt + F11 to open the VB editor and copy the code below to the big
code window. If the code window is dark, on the menu bar click
Insert>Module.

Sub divByTwo()
Dim lr As Long, rng As Range, sh As Worksheet, c As Range
Set sh = ActiveSheet
lr = sh.Cells(Rows.Count, 2).End(xlUp).Row
Set rng = sh.Range("B2:B" & lr)
For Each c In rng
If c.Value = 42 Then
c.Offset(0, 3) = c.Offset(0, 3).Value/2
End If
Next
End Sub

 

[Mike H]

Anne,

I have no idea where my previous post went, try this

Sub Servient()
Dim MyRange As Range, c As Range
LastRow = Cells(Rows.Count, "B").End(xlUp).Row
Set MyRange = Range("B2:B" & LastRow)
For Each c In MyRange
If c.Value = 42 Then
c.Offset(, 3).Value = c.Offset(, 3).Value / 2
End If
Next
End Sub

Mike

 

[Anne]

Hi Simon!
Well, I'm trying to make a very complex process of working with many Excel
files easy for non-Excel folks to use, so I thought a macro button called
"Fund42DivideBy2" would be helpful for them.

The 42 in column B is an accounting Fund number. It's formatted as a number
value when it comes over from the data warehouse. There may be Fund 41s, 42s
and 43s in column B. I only want the ones that are Fund 42 to have their
amount paid (col. E) divided by 2.

Hopefully that helps a little.
Anne

 

[Howard31]

Hi Anne,

The thing is if you run the following macro it will divide amount paid,
however if you run it again it will divide it again so let say 100 will
become 50 and next time you run it it will become 25, if this is not what
you want happening then use my second macro which will record each row if it
has already been divided by entering the word "Done" in column F, so if you
add more data after running the first time you can run it again and it will
only affect the new data.

If it doesn’t work the way you like please let me know!


Macro1
---------------------------
Sub DevideIf42()
Dim Sht As Worksheet, Rng As Range, MyRng As Range
Dim LastRow As Long

Set Sht = ThisWorkbook.Worksheets("Sheet1")

With Sht
LastRow = .Cells(.Rows.Count, 2).End(xlUp)

Set MyRng = Sht.Range(.Cells(1, 2), .Cells(LastRow, 2))

For Each Rng In MyRng
If Rng.Value = 42 Then Rng.Offset(0, 3).Value = Rng.Offset(0,
3).Value / 2
Next Rng
End With
End Sub
--------------------------------
Macro2
------------------------------------
Sub DevideIf42AndNotDividedYet()
Dim Sht As Worksheet, Rng As Range, MyRng As Range
Dim LastRow As Long

Set Sht = ThisWorkbook.Worksheets("Sheet1")

With Sht
LastRow = .Cells(.Rows.Count, 2).End(xlUp)

Set MyRng = Sht.Range(.Cells(1, 2), .Cells(LastRow, 2))

For Each Rng In MyRng
If Rng.Offset(0, 4) = "Done" Then
'Do nothing
Else
If Rng.Value = 42 Then
Rng.Offset(0, 3).Value = Rng.Offset(0, 3).Value / 2
Rng.Offset(0, 4) = "Done"
End If
End If
Next Rng
End With
End Sub

 

[Anne]

Thanks!! Thanks, Simon, Mike H and JLGWhiz! That works just fine. Really
appreciate it.
Anne

 

[john]

Anne,
here is another approach using find. Procedure should divide values in Col E
by two as required but also, it will ensure that this is done once only by
making 42 value bold. When run again, bold values are ignored.

Hope helpful

Sub DevideIf42()
Dim c As Range
Dim FirstAddress As String
With Worksheets(1).Range("B:B")

Set c = .Find(42, LookIn:=xlValues)

If Not c Is Nothing Then

FirstAddress = c.Address

Do
If c.Font.Bold = False Then

c.Offset(0, 3) = c.Offset(0, 3).Value / 2

c.Font.Bold = True

End If

Set c = .FindNext(c)

If c Is Nothing Then Exit Do

Loop Until c.Address = FirstAddress

End If

End With

End Sub

 

[JLGWhiz]

Hi Anne, I believe someone else pointed out that as long as you only use
that sheet with those values once, it will be OK to use most of the codes
that several of us offered. But, if you run the code before removing the
changed records, or in some way marking them as having already been halved
in column E, then the next time you run the macro, it will halve those
values again, making them 1/4 of the original value.
If the Fund code changes automatically after it is halved, no problem.
Otherwise, be careful.