I am a complete beginner to VB so all help is much appreciated!!!! I have
been given the following code to update a cell by a percentage uplift:

Sub ibpc()
Dim pc As Range
Dim ib As Range
Set pc = Range("G4") '10%
Set ib = Range("F4") '100
ib = ib * (pc + 1) '110
End Sub

This works fine for one cell but I need to update multiple cells. I have
tried set ib = F4:F20 but this gives error which points back to this line.

Any ideas how to update more than 1 cell by the %. These cells will be
changing dynamically as they come from a analysis services cube. These cells
will all be in one column but there could be x amount of rows to be updated.
Only way I could think of would be to check if the cell 0 in the VB code
and multiply by % then?


John

Sorry: I don't understand why don't you just have a formula in the
cells in the column?


On 21 Jun, 10:58, johnprez wrote:
I am a complete beginner to VB so all help is much appreciated!!!! I have
been given the following code to update a cell by a percentage uplift:

Sub ibpc()
Dim pc As Range
Dim ib As Range
Set pc = Range("G4") '10%
Set ib = Range("F4") '100
ib = ib * (pc + 1) '110
End Sub

This works fine for one cell but I need to update multiple cells. I have
tried set ib = F4:F20 but this gives error which points back to this line.

Any ideas how to update more than 1 cell by the %. These cells will be
changing dynamically as they come from a analysis services cube. These cells
will all be in one column but there could be x amount of rows to be updated.
Only way I could think of would be to check if the cell 0 in the VB code
and multiply by % then?

John

The reason for not putting formulas in the cells is because the data is
coming from a cube and this worksheet is used to writeback to the cube so
these cells can be manually entered or I want to have a button that uplifts
by a percentage....

Thanks....

"WhytheQ" wrote:

Sorry: I don't understand why don't you just have a formula in the
cells in the column?


On 21 Jun, 10:58, johnprez wrote:
I am a complete beginner to VB so all help is much appreciated!!!! I have
been given the following code to update a cell by a percentage uplift:

Sub ibpc()
Dim pc As Range
Dim ib As Range
Set pc = Range("G4") '10%
Set ib = Range("F4") '100
ib = ib * (pc + 1) '110
End Sub

This works fine for one cell but I need to update multiple cells. I have
tried set ib = F4:F20 but this gives error which points back to this line.

Any ideas how to update more than 1 cell by the %. These cells will be
changing dynamically as they come from a analysis services cube. These cells
will all be in one column but there could be x amount of rows to be updated.
Only way I could think of would be to check if the cell 0 in the VB code
and multiply by % then?

John

use this

Sub ibpc()
Dim pc As Range
Dim ib As Range
Set pc = Range("G4") '10%
Set ib = Range("F4:F20") '100
For Each cell In ib
cell = cell * (pc + 1) '110
Next cell
End Sub

"johnprez" wrote:

I am a complete beginner to VB so all help is much appreciated!!!! I have
been given the following code to update a cell by a percentage uplift:

Sub ibpc()
Dim pc As Range
Dim ib As Range
Set pc = Range("G4") '10%
Set ib = Range("F4") '100
ib = ib * (pc + 1) '110
End Sub

This works fine for one cell but I need to update multiple cells. I have
tried set ib = F4:F20 but this gives error which points back to this line.

Any ideas how to update more than 1 cell by the %. These cells will be
changing dynamically as they come from a analysis services cube. These cells
will all be in one column but there could be x amount of rows to be updated.
Only way I could think of would be to check if the cell 0 in the VB code
and multiply by % then?


John

Tried the code but it gives an error that cell in undefined?

Thanks for your help by the way!!

"Joel" wrote:

use this

Sub ibpc()
Dim pc As Range
Dim ib As Range
Set pc = Range("G4") '10%
Set ib = Range("F4:F20") '100
For Each cell In ib
cell = cell * (pc + 1) '110
Next cell
End Sub

"johnprez" wrote:

I am a complete beginner to VB so all help is much appreciated!!!! I have
been given the following code to update a cell by a percentage uplift:

Sub ibpc()
Dim pc As Range
Dim ib As Range
Set pc = Range("G4") '10%
Set ib = Range("F4") '100
ib = ib * (pc + 1) '110
End Sub

This works fine for one cell but I need to update multiple cells. I have
tried set ib = F4:F20 but this gives error which points back to this line.

Any ideas how to update more than 1 cell by the %. These cells will be
changing dynamically as they come from a analysis services cube. These cells
will all be in one column but there could be x amount of rows to be updated.
Only way I could think of would be to check if the cell 0 in the VB code
and multiply by % then?


John

Do you have the Option Explicit statementt in you code? You have to add a
declaration for cell.

Dim cell As Range

"johnprez" wrote:

Tried the code but it gives an error that cell in undefined?

Thanks for your help by the way!!

"Joel" wrote:

use this

Sub ibpc()
Dim pc As Range
Dim ib As Range
Set pc = Range("G4") '10%
Set ib = Range("F4:F20") '100
For Each cell In ib
cell = cell * (pc + 1) '110
Next cell
End Sub

"johnprez" wrote:

I am a complete beginner to VB so all help is much appreciated!!!! I have
been given the following code to update a cell by a percentage uplift:

Sub ibpc()
Dim pc As Range
Dim ib As Range
Set pc = Range("G4") '10%
Set ib = Range("F4") '100
ib = ib * (pc + 1) '110
End Sub

This works fine for one cell but I need to update multiple cells. I have
tried set ib = F4:F20 but this gives error which points back to this line.

Any ideas how to update more than 1 cell by the %. These cells will be
changing dynamically as they come from a analysis services cube. These cells
will all be in one column but there could be x amount of rows to be updated.
Only way I could think of would be to check if the cell 0 in the VB code
and multiply by % then?


John

Thanks Joel your a star that works fine thanks pal....

One question within that :

"For each cell in ib"

Can I add an if statement ? I need to add something that tests if there is a
value in the cell ...say 0 for example.....

Thanks pal

"Joel" wrote:

Do you have the Option Explicit statementt in you code? You have to add a
declaration for cell.

Dim cell As Range

"johnprez" wrote:

Tried the code but it gives an error that cell in undefined?

Thanks for your help by the way!!

"Joel" wrote:

use this

Sub ibpc()
Dim pc As Range
Dim ib As Range
Set pc = Range("G4") '10%
Set ib = Range("F4:F20") '100
For Each cell In ib
cell = cell * (pc + 1) '110
Next cell
End Sub

"johnprez" wrote:

I am a complete beginner to VB so all help is much appreciated!!!! I have
been given the following code to update a cell by a percentage uplift:

Sub ibpc()
Dim pc As Range
Dim ib As Range
Set pc = Range("G4") '10%
Set ib = Range("F4") '100
ib = ib * (pc + 1) '110
End Sub

This works fine for one cell but I need to update multiple cells. I have
tried set ib = F4:F20 but this gives error which points back to this line.

Any ideas how to update more than 1 cell by the %. These cells will be
changing dynamically as they come from a analysis services cube. These cells
will all be in one column but there could be x amount of rows to be updated.
Only way I could think of would be to check if the cell 0 in the VB code
and multiply by % then?


John

The for loop can be as large as you need. My for loops are usually 20 - 30
lines of code. You can also exit out of a for loop with a "exit for"
statement.

"johnprez" wrote:

Thanks Joel your a star that works fine thanks pal....

One question within that :

"For each cell in ib"

Can I add an if statement ? I need to add something that tests if there is a
value in the cell ...say 0 for example.....

Thanks pal

"Joel" wrote:

Do you have the Option Explicit statementt in you code? You have to add a
declaration for cell.

Dim cell As Range

"johnprez" wrote:

Tried the code but it gives an error that cell in undefined?

Thanks for your help by the way!!

"Joel" wrote:

use this

Sub ibpc()
Dim pc As Range
Dim ib As Range
Set pc = Range("G4") '10%
Set ib = Range("F4:F20") '100
For Each cell In ib
cell = cell * (pc + 1) '110
Next cell
End Sub

"johnprez" wrote:

I am a complete beginner to VB so all help is much appreciated!!!! I have
been given the following code to update a cell by a percentage uplift:

Sub ibpc()
Dim pc As Range
Dim ib As Range
Set pc = Range("G4") '10%
Set ib = Range("F4") '100
ib = ib * (pc + 1) '110
End Sub

This works fine for one cell but I need to update multiple cells. I have
tried set ib = F4:F20 but this gives error which points back to this line.

Any ideas how to update more than 1 cell by the %. These cells will be
changing dynamically as they come from a analysis services cube. These cells
will all be in one column but there could be x amount of rows to be updated.
Only way I could think of would be to check if the cell 0 in the VB code
and multiply by % then?


John