Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SumIf - when I fill down the Range, Criteria & sum range changes | Excel Worksheet Functions | |||
Fill Range With A Value | Excel Worksheet Functions | |||
How to fill a range with data from another range? | Excel Discussion (Misc queries) | |||
Range doesn't fill | Excel Programming | |||
Fill down range | Excel Programming |