How can I make a Macro work multiple rows instead of one?

  • Thread starter Thread starter cimbom
  • Start date Start date
C

cimbom

Hi all,
The macro below is for a basic calculation as shown:
G H I J
Inventory Orders Safety Production
45 25 10 -10 (H3+I3-G3)

If the Production J3 is negative, change the safety (I3) to a number
so that Production will be 0. In this case, Safety should be 20 in
order for Production to be 0.
The Macro below works perfectly, but it only changes this specific
cells (I3 and J3). I have 300 rows. How can I change this Macro below
so that when I run it, it affects 300 rows instead of just 1?
Thank you

Sub Solve()


'J3=production cell
a = Range("J3").Value


'I3=safety cell
b = Range("I3").Value


If a < 0 Then


Range("I3").Value = b + a * -1
End If


End Sub
 
Hi,

Try this way:

Sub Solve()
Dim xCell As Range, xRange As Range, a As Long
Set xRange = Range("I3:I300")'Change reference if necessary
For Each xCell In xRange
With xCell
a = .Offset(0, 1).Value
If a < 0 Then .Value = .Value - a
End With
Next
End Sub

Best regards from France,

Manu/
 
This will work the equation you posted for each
cell in Column J that has a value of less than
zero (negative value). I assumed that the multiplier
of -1 was to make column J a positive number, If that
assumption is in error, then remove the outer parentheses
from b = b + (a * (-1)). However, that could result in a
value of zero, if you do.

Sub adjColJ()
lr = Cells(Rows.Count, 10).End(xlUp).Row
For i = 3 To lr
a = Cells(i, 10) 'Column J
b = Cells(i, 10).Offset(0, -1) 'Column I
If a < 0 Then
b = b + (a * (-1)) 'Changes a to pos # and adds to b
End If
Next i
End Sub
 
Hi,

Try this way:

Sub Solve()
Dim xCell As Range, xRange As Range, a As Long
Set xRange = Range("I3:I300")'Change reference if necessary
For Each xCell In xRange
With xCell
a = .Offset(0, 1).Value
If a < 0 Then .Value = .Value - a
End With
Next
End Sub

Best regards from France,

Manu/

"cimbom" <[email protected]> a écrit dans le message de (e-mail address removed)...











- Show quoted text -
Click to expand...

Hi Manu, thanks for your reply. When I run the Macro you sent me it
says Run-Time error "13" Type Mismatch and when I debug it, it
highlights a = .Offset(0, 1).Value. How can I correct it? Thanks
again.
 
Hi,

May be you include the header row in the reference ?

I tested without error, it should work ..

Regards,

Manu/

"cimbom" <[email protected]> a écrit dans le message de (e-mail address removed)...
Hi,

Try this way:

Sub Solve()
Dim xCell As Range, xRange As Range, a As Long
Set xRange = Range("I3:I300")'Change reference if necessary
For Each xCell In xRange
With xCell
a = .Offset(0, 1).Value
If a < 0 Then .Value = .Value - a
End With
Next
End Sub

Best regards from France,

Manu/

"cimbom" <[email protected]> a écrit dans le message de (e-mail address removed)...











- Show quoted text -
Click to expand...

Hi Manu, thanks for your reply. When I run the Macro you sent me it
says Run-Time error "13" Type Mismatch and when I debug it, it
highlights a = .Offset(0, 1).Value. How can I correct it? Thanks
again.
 
Back
Top