Excel Macro - Calc to last row problem

[mickw]

This is my first post. I am new to excel macros and need some hel
please.

I need to create a macro that I can run every month on that month'
invoices.

I can do all the manipluations I want, apart from one type. I want t
create a column with a formula from row 2 to the last row of data
Because the number of rows varies each month I cannot see how t
achieve this.

Example

Column A = invoice number
Column B = invoice value
Column C = invoice sign (SI or SC)

I want to multiply the invoice value by -1 if the invoice sign is S
for every row that contains an invoice number.

Any help would be appreciated.

Cheers,

Mic

 

[Bob Phillips]

Hi Mick,

Here it is

Sub TestLoopUp()
Dim i As Long

For i = 1 To Cells(Rows.Count, "A").End(xlUp).Row
If Cells(i, "A").Offset(0, 2).Value = "SC" Then
Cells(i, "D").Value = Cells(i, "B").Value * -1
End If
Next i

End Sub


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[iomighty]

Hi Bob,

I read these forumns everyday as my major source of learning Excel.
Even though I am not trying to what Mick is doing here, I was curiou
how I good edit that code to have the desired result end up on a secon
worksheet in the same workbook.

Curious to know if you have the time.

Thanks,
Mat

 

[Bob Phillips]

Matt,

All that you need to do is to precede the destination cell with the
worksheet name. As it was written all of the statements assumed the
activesheet. It could have been written to explicitly define the
activesheet, or a named sheet.

Sub TestLoopUp()
Dim i As Long

For i = 1 To Cells(Rows.Count, "A").End(xlUp).Row
If Cells(i, "A").Offset(0, 2).Value = "SC" Then
Worksheets("Sheet3").Cells(i, "D").Value = Cells(i, "B").Value
* -1
End If
Next i

End Sub

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[iomighty]

Thanks Bob,

Info like this is how I am building my Excel knowledge. Unfortuantel
I am teaching myself by looking at poeple's examples and then trying t
replicate them to understand how the instructions work. This ma
sound like a naive question, but when you write " If Cells(i
"A").Offset(0, 2).Value" in the above example: - What does th
"Offset(0,2).Value" relate to.
I have used it a few times in building simple instructions withou
knowing exactly its' reference.

And again I want to express the appreciation I have for so many peopl
on this forumn that take the time every day to answer these od
questions. I have learned so much here, more so than from the books
use for reference.

Cheers,
Mat

 

[Bob Phillips]

Matt,

What is happening here is that often we have a way, in code, of identifying
a particular instance, say the last cell in column A that has data in it
(which is what the Cells(Rows.Count, "A").End(xlUp).Row does, or at least
the row of that). But the cell that you can identify may not be the cell
that you want to change, it may be some columns to the left or right, or
rows above or below. This is where you find Offset useful.

Offset(x,y) will identify a cell x rows and y columns beyond the cell
already identified (note that x can be negative for rows above, y can be
negative for columns left). So in my example, having found the cell in
column A (Cells(i, "A")), I use offset to get the cell 2 columns to the
right (.Offset(0, 2)).

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[mickw]

Bob

Thank you for the excellent solution.

To get the invoice value when not equal to SC I added another line -

Else: Cells(i, "D").Value = Cells(i, "B").Value

before the End If. This seemed to do the job.

And looking out across Poole Harbour to the Purbecks sounds infinitel
better than looking at Basinsgtoke Hospital!

Regards

Mic

 

[Bob Phillips]

Mick,

That's good, your first post satisfactorily resolved. Look forward to seeing
more of you (when I'm not looking at those hills of course).

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)