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

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

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

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" a écrit dans le message de news:
...
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

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

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

"cimbom" wrote:

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

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

On May 31, 7:14 pm, "tissot.emmanuel" wrote:
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" a écrit dans le message de news:
om...



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- Hide quoted text -

- Show quoted text -

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.

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

Hi,

May be you include the header row in the reference ?

I tested without error, it should work ..

Regards,

Manu/

"cimbom" a écrit dans le message de news:
...
On May 31, 7:14 pm, "tissot.emmanuel" wrote:
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" a écrit dans le message de news:
om...



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- Hide quoted text -

- Show quoted text -

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.