Pricechecker

[chriscorpion786]

Hi all there ,


can you help with some VBA code for the following:


In column A i have some numbers between 1 and 12 which represents units, and column B has the price.

In column C-N which starts from January i need to have the result of units * price but the cells should be filled in such a manner that if unit in column A is 5 then the result should be filled from Jan - May for 5 columns and so on.

I have done it in Excel but i need a VBA version for this.


Kindly help..


Mustafa

 

[Luke M]

How's this?

[pre]

Sub FillPrice()
Dim StartRow As Integer
Dim EndRow As Integer

'What row does data start on?
StartRow = 2

'Calculates the last row
EndRow = Range("A65536").End(xlUp).Row
Application.ScreenUpdating = False
For i = StartRow To EndRow
With Cells(i, "A")
Range(Cells(i, "C"), .Offset(0, 1 + .Value)).Value = _
.Value * .Offset(0, 1).Value
End With
Next
Application.ScreenUpdating = True
End Sub

[/pre]

 

[chriscorpion786]

Hi Luke,


It worked...thanks so much...if you dont mind can you explain this code:


Range(Cells(i, "C"), .Offset(0, 1 + .Value)).Value = _

.Value * .Offset(0, 1).Value


im a beginner, so i need to understand and practise this myself......


Thanks so much....why does VBA seem so difficult to learn.........


Mustafa

 

[Luke M]

Sure thing.

The previous line called out a With statement. This is used so that you don't have to keep writing out long repetitive lines of coding, which in turn helps things run a little faster. That said, remember that ever part that starts with just a "." is actually modifying the With object. In this case, it's the cell in col A of whichever row we're looking at (determined by variable i).


So, let's look at left side of equation:

Range(Cells(i, "C"), .Offset(0, 1 + .Value)).Value


We want to define some range, and set the Value for it. Range can have 2 arguments, defining the top-left and bottom-right cells. The top left cell is going to be in col C, of row i. That's what this part of the code says:

Cells(i, "C")


The bottom-right cell will be partly determined by the value in col A. Assume col A has a value of 5. Remembering that we need to skip col B (which has price), we need to "offset" 5+1 columns from col A and 0 rows (we're staying in the same row). That's what this part says:

.Offset(0, 1 + .Value)


Now, onto the right hand side of equation, which is easier.

.Value * .Offset(0, 1).Value


This says to take the value of the cell in col A, and multiply by the value of the cell 1 column to the right of col A (aka, col B). Alternatively, we could write:

.Value * .Cells(i,"B").Value

 

[chriscorpion786]

Thanks so much.....Luke......that makes my life a little easier.....


Mustafa