fill range

[Basta1980]

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

 

[Sam Wilson]

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]

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]

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?

 

[Mike Fogleman]

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