Using Formulas in VBA Macro

[Sam Fowler]

Hi:
I have a spreadsheet that summarizes an inventory and
also gives me the opportunity to make several comments on
different areas of the inventory.

I am trying to get a macro to select a single cell (o39),
which is the grand total result (Dollars + and/or -),
then make a comparison to the amount that should be
there, (Ledger Amount) Cell (o38).

After that is completed, I want the macro to calculate
the severity of the overage/shortage. ie: 2% over, 5%
over, 5% Short, etc., then select another sheet where pre
written comments are stored (Recommendation Worksheet),
and select one of 8 different comments. 4 are for
overages based on % of overage and 4 are for shortages.
Can anyone help me get this to work, or am I barking up
the wrong tree.

I am including the macro code I am trying to use.


Thanks,

Sam

'Overages

Sheets("2004").Select
Range("o39").Select
If ActiveCell.Value > (".001") And ActiveCell.Value <
("o38*1.02") Then
Sheets("Recommendation Worksheet").Select
Range("BM2").Select
Selection.Copy
Sheets("2004").Select
Range("A464").Select
ActiveSheet.Paste
Range("A464").Select
End If
Sheets("2004").Select
Range("o39").Select
If ActiveCell.Value > ("o38*1.20") And
ActiveCell.Value < ("o38*1.05") Then
Sheets("Recommendation Worksheet").Select
Range("CC2").Select
Selection.Copy
Sheets("2004").Select
Range("A464").Select
ActiveSheet.Paste
Range("A464").Select
End If
Sheets("2004").Select
Range("o39").Select
If ActiveCell.Value > ("o38*1.05") And
ActiveCell.Value < ("o38*1.075") Then
Sheets("Recommendation Worksheet").Select
Range("CC2").Select
Selection.Copy
Sheets("2004").Select
Range("A464").Select
ActiveSheet.Paste
Range("A464").Select

End If

Sheets("2004").Select
Range("o39").Select
If ActiveCell.Value > ("o38*1.075") Then
Sheets("Recommendation Worksheet").Select
Range("CS2").Select
Selection.Copy
Sheets("2004").Select
Range("A464").Select
ActiveSheet.Paste
Range("A464").Select
End If





' Shortages

' Sheets("2004").Select
' Range("o25").Select
' If ActiveCell < 0 Then
' Sheets("Recommendation Worksheet").Select
' Range("A2").Select
' Selection.Copy
' Sheets("2004").Select
' Range("A464").Select
' ActiveSheet.Paste
'End If

' Sheets("2004").Select
' Range("o25").Select
' If ActiveCell < -2.01 Then
' Sheets("Recommendation Worksheet").Select
' Range("q2").Select
' Selection.Copy
' Sheets("2004").Select
' Range("A464").Select
' ActiveSheet.Paste
'End If
' Sheets("2004").Select
' Range("o25").Select
' If ActiveCell < -5.01 Then
' Sheets("Recommendation Worksheet").Select
' Range("ag2").Select
' Selection.Copy
' Sheets("2004").Select
' Range("A464").Select
' ActiveSheet.Paste
'End If
' Sheets("2004").Select
' Range("o25").Select
' If ActiveCell < -8.01 Then
' Sheets("Recommendation Worksheet").Select
' Range("aw2").Select
' Selection.Copy
' Sheets("2004").Select
' Range("A464").Select
' ActiveSheet.Paste
' Range("A464").Select
'End If

 

[mudraker]

Sam

Try this - Not Tested




Dim wsRec As Worksheet

Dim rngO39 As Range
Dim rngO38 As Range
Dim rngA464 As Range

Set wsRec = ThisWorkbook.Sheets("Recommendation Worksheet")

Set rngO38 = ThisWorkbook.Sheets("2004").Range("038")
Set rngO39 = ThisWorkbook.Sheets("2004").Range("039")
Set rngA464 = ThisWorkbook.Sheets("2004").Range("A464")

If rngO39.Value > 0.001 Then
If rng039.Value < rngO38.Value * 1.02 Then
rngA464.Value = wsRec.Range("BM2").Value
ElseIf rngO39.Value > rngO38.Value * 1.2 And rngO39.Value
rng038.Value * 1.05 Then
rngA464.Value = wsRec.Range("CC2").Value
ElseIf rngO39.Value > wso38.Value * 1.05 And rngO39.Value < rngO38
1.075 Then
rngA464.Value = wsRec.Range("CC2").Value
ElseIf rngO39.Value > rngO38.Value * 1.075 Then
rngA464.Value = wsRec.Range("CS2").Value
End If
End I

 

[Guest]

Mudraker:

Thanks, that works perfectly.

Sam