fill range
 

Hi all,

See code below. How do I add a command that performs not only a calculation
on activecell.offset(0,1), which is in this case B1, but also calculates C1
through to K1?!

Sub Zoek_Ban1()

Range("A1").Select
Do While ActiveCell.Value < Empty
If ActiveCell.Offset(0, 0).Value 23 Then
ActiveCell.Offset(0, 1).Value = (ActiveCell.Offset(0, 1).Value / 100) *
0.75
ElseIf ActiveCell.Offset(0, 0).Value < 23 Then
ActiveCell.Offset(0, 1).Value = ActiveCell.Offset(0, 1).Value + 13.08
End If
ActiveCell.Offset(1, 0).Select
Loop
End Sub

Regards,

Basta1980

fill range
 

Try this:

Sub Zoek_Ban1()

Dim i As Integer
Dim j As Integer

With Range("A1")
Do While .Offset(i, 0).Value < Empty
If .Offset(i, 0).Value 23 Then
For j = 1 To 10
.Offset(i, j).Value = .Offset(i, j).Value * 0.75 / 100
Next j
Else
For j = 1 To 10
.Offset(i, j).Value = .Offset(i, j).Value + 13.08
Next j
End If

i = i + 1
Loop
End With

End Sub

"Basta1980" wrote:

Hi all,

See code below. How do I add a command that performs not only a calculation
on activecell.offset(0,1), which is in this case B1, but also calculates C1
through to K1?!

Sub Zoek_Ban1()

Range("A1").Select
Do While ActiveCell.Value < Empty
If ActiveCell.Offset(0, 0).Value 23 Then
ActiveCell.Offset(0, 1).Value = (ActiveCell.Offset(0, 1).Value / 100) *
0.75
ElseIf ActiveCell.Offset(0, 0).Value < 23 Then
ActiveCell.Offset(0, 1).Value = ActiveCell.Offset(0, 1).Value + 13.08
End If
ActiveCell.Offset(1, 0).Select
Loop
End Sub

Regards,

Basta1980

fill range
 

HI Sam,

This works perfect. One more thing though. In column A there's a list (from
0 tot 26). When I run the code now it stops at 0 (so 15 to ) are
recalculated). How can I include 0 so that 0 to 15 is recalculated too?!

15
16
17
18
19
20
21
22
23
24
25
26
2
1
2
3
4
5
6
7
8
9
10
11


"Sam Wilson" wrote:

Try this:

Sub Zoek_Ban1()

Dim i As Integer
Dim j As Integer

With Range("A1")
Do While .Offset(i, 0).Value < Empty
If .Offset(i, 0).Value 23 Then
For j = 1 To 10
.Offset(i, j).Value = .Offset(i, j).Value * 0.75 / 100
Next j
Else
For j = 1 To 10
.Offset(i, j).Value = .Offset(i, j).Value + 13.08
Next j
End If

i = i + 1
Loop
End With

End Sub

"Basta1980" wrote:

Hi all,

See code below. How do I add a command that performs not only a calculation
on activecell.offset(0,1), which is in this case B1, but also calculates C1
through to K1?!

Sub Zoek_Ban1()

Range("A1").Select
Do While ActiveCell.Value < Empty
If ActiveCell.Offset(0, 0).Value 23 Then
ActiveCell.Offset(0, 1).Value = (ActiveCell.Offset(0, 1).Value / 100) *
0.75
ElseIf ActiveCell.Offset(0, 0).Value < 23 Then
ActiveCell.Offset(0, 1).Value = ActiveCell.Offset(0, 1).Value + 13.08
End If
ActiveCell.Offset(1, 0).Select
Loop
End Sub

Regards,

Basta1980

fill range
 

Hi,

I'm not too sure what you mean but I'll try.

The macro should go down column A until it gets to an empty cell - each time
it moves to a new cell it inspects it to see if it's over 23. If it is it
multiplies the 10 cells to the right by 0.75/100, otherwise it adds 13.8 to
them.

If it stops unexpectedly then you must have a blank somewhere - is there a
hidden row?

"Basta1980" wrote:

HI Sam,

This works perfect. One more thing though. In column A there's a list (from
0 tot 26). When I run the code now it stops at 0 (so 15 to ) are
recalculated). How can I include 0 so that 0 to 15 is recalculated too?!

15
16
17
18
19
20
21
22
23
24
25
26
2
1
2
3
4
5
6
7
8
9
10
11


"Sam Wilson" wrote:

Try this:

Sub Zoek_Ban1()

Dim i As Integer
Dim j As Integer

With Range("A1")
Do While .Offset(i, 0).Value < Empty
If .Offset(i, 0).Value 23 Then
For j = 1 To 10
.Offset(i, j).Value = .Offset(i, j).Value * 0.75 / 100
Next j
Else
For j = 1 To 10
.Offset(i, j).Value = .Offset(i, j).Value + 13.08
Next j
End If

i = i + 1
Loop
End With

End Sub

"Basta1980" wrote:

Hi all,

See code below. How do I add a command that performs not only a calculation
on activecell.offset(0,1), which is in this case B1, but also calculates C1
through to K1?!

Sub Zoek_Ban1()

Range("A1").Select
Do While ActiveCell.Value < Empty
If ActiveCell.Offset(0, 0).Value 23 Then
ActiveCell.Offset(0, 1).Value = (ActiveCell.Offset(0, 1).Value / 100) *
0.75
ElseIf ActiveCell.Offset(0, 0).Value < 23 Then
ActiveCell.Offset(0, 1).Value = ActiveCell.Offset(0, 1).Value + 13.08
End If
ActiveCell.Offset(1, 0).Select
Loop
End Sub

Regards,

Basta1980

fill range
 

A value of 0 = Empty. Change the following line of code:
Do While .Offset(i, 0).Value < Empty
to
Do While .Offset(i, 0).Value < ""

Mike F

"Basta1980" wrote in message
...
HI Sam,

This works perfect. One more thing though. In column A there's a list
(from
0 tot 26). When I run the code now it stops at 0 (so 15 to ) are
recalculated). How can I include 0 so that 0 to 15 is recalculated too?!

15
16
17
18
19
20
21
22
23
24
25
26
2
1
2
3
4
5
6
7
8
9
10
11


"Sam Wilson" wrote:

Try this:

Sub Zoek_Ban1()

Dim i As Integer
Dim j As Integer

With Range("A1")
Do While .Offset(i, 0).Value < Empty
If .Offset(i, 0).Value 23 Then
For j = 1 To 10
.Offset(i, j).Value = .Offset(i, j).Value * 0.75 / 100
Next j
Else
For j = 1 To 10
.Offset(i, j).Value = .Offset(i, j).Value + 13.08
Next j
End If

i = i + 1
Loop
End With

End Sub

"Basta1980" wrote:

Hi all,

See code below. How do I add a command that performs not only a
calculation
on activecell.offset(0,1), which is in this case B1, but also
calculates C1
through to K1?!

Sub Zoek_Ban1()

Range("A1").Select
Do While ActiveCell.Value < Empty
If ActiveCell.Offset(0, 0).Value 23 Then
ActiveCell.Offset(0, 1).Value = (ActiveCell.Offset(0, 1).Value /
100) *
0.75
ElseIf ActiveCell.Offset(0, 0).Value < 23 Then
ActiveCell.Offset(0, 1).Value = ActiveCell.Offset(0, 1).Value +
13.08
End If
ActiveCell.Offset(1, 0).Select
Loop
End Sub

Regards,

Basta1980