Help needed with excel macro problem!

Printable View

10-22-2007, 03:44 PM
Lester

Help needed with excel macro problem!

Hello All
My first post here so here goes...I'm really new to VBA and need some help to write a macro to do a simple calculation (I tried to attach a screen-grab of the file but moderators won't let me do this until my post count hits 5, nor will they allow me to include links... :banghead: ). Can I get round them? [Apologies to mods in advance!] Here goes...replace UPPER-CASE letters with approriate symbols:

httpCOLONSLASHSLASHi122.photobucket.comSLASHalbumsSLASHo250SLASHmaz1889SLAS Hscreen-grab-excel.jpg

In the excel file, I want to create a macro that will perform a simple multiplication whenever 'Name=Barry' and 'Category=High', such that the value in the 'Hours' column is multiplied by (85/65). This value will be populated in the adjacent column 'Adjusted Hours' (which can be empty prior to the macro running...actually it could be created BY the macro, if need be...it could even REPLACE the value in the 'Hours' column, so I'm flexible about that).

Can anyone help me do this? The only exprience I've had with macros is with the 'record macro' facility in excel but have never done anything that involves things like conditional 'IF-statements'.

Thanks for your help. It's much appreciated.

Lester
10-22-2007, 03:58 PM
Bob Phillips

[vba]

Public Sub ProcessData()
Dim i As Long
Dim iLastRow As Long

With ActiveSheet

iLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
iStart = 1
For i = 2 To iLastRow
If .Cells(i, "A").Value = "Barry" And _
.Cells(i, "B").Value = "High" Then
.Cells(i, "D").Value = .Cells(i, "C").Value * 85 / 65
Else
.Cells(i, "D").Value = .Cells(i, "C").Value
End If
Next i
End With

End Sub
[/vba]
10-23-2007, 01:28 AM
Lester

Muchas gracias por su ayuda, xld. Me alegra de verdad que me haya ayudado. Voy a probar el c?digo de arriba - esperemos que me sirva. Ud es muy amable.
---
Thanks for your help, xld. I'm really pleased that you've helped me. I'm going to try the above code - let's hope it works ok. You're very kind. (...and, yes, I speak reasonable Spanish!)

Un saludo
Lester
10-23-2007, 01:38 AM
Bob Phillips

It may not work exactly as you want as it only seems to cater for one condition, but when you try it you should be able to be clear on what else it needs to do (which I couldn't get from the jpg)>

BTW, why did you munge the URL, it's only a share site?
10-23-2007, 01:49 AM
Lester

Quote:

Originally Posted by xld

It may not work exactly as you want as it only seems to cater for one condition, but when you try it you should be able to be clear on what else it needs to do (which I couldn't get from the jpg)>

BTW, why did you munge the URL, it's only a share site?

The moderators of this forum would not allow me to include a 'link' in my post, hence I 'disguised' it.
Many thanks.
10-23-2007, 01:57 AM
Lester

Another newbie question...which VBA window do I paste the code into? Do I go (in excel) to Tools|Macros|Visual Basic Editor, then paste it in the window to the right? I ask because there is already the code of a recorded macro in this window.
10-23-2007, 02:11 AM
Bob Phillips

They only stop you adding a link because you are new, you need 5 posts, not because links are forbidden. Strange rule, but there you are.

If there is recorded code in that code pane, that is the place to put it. You then need to run it, in excel use Alt-F8 and select it from the list, and hit the Run button.
10-23-2007, 02:32 AM
Lester

Many thanks again.
10-23-2007, 03:09 AM
TonyJollans

Quote:

Originally Posted by xld

They only stop you adding a link because you are new, you need 5 posts, not because links are forbidden. Strange rule, but there you are.

I presume ... it stops a lot of automated spam by one-time users with links to all manner of dodgy sites.
10-23-2007, 03:10 AM
Lester

xld...Genial! Funciona muy bien. Tuve que modificarlo un poquito, pero me sirve de mucha ayuda. Cheers.
10-23-2007, 04:01 AM
Bob Phillips

Quote:

Originally Posted by TonyJollans

I presume ... it stops a lot of automated spam by one-time users with links to all manner of dodgy sites.

Most spammers would hardly be deterred by that, 5 posts is nothing when you send millions.
10-23-2007, 12:15 PM
TonyJollans

They don't post to forums in quite the same way they send spam e-mail and they don't expect to get the chance to make multiple posts. They have automated programs that sign up, respond to emails, and send a post. Next time they just use the same process all over again with a new id. It's automated - sending 5 posts I suspect needs manual intervention which they have neither time nor inclination for - they just go for easier targets. I'm sure it's not 100% or foolproof but it is a deterrent.
10-23-2007, 12:46 PM
Bob Phillips

I'll take your word for it Tony. I would hope that the forums have Bayesian filters as well to trap the junk that does hit. Luckily it seems to work. Have you noticed all the KEYGEN posts hitting the forums recently, including on it shouldn't do?
10-23-2007, 04:19 PM
Lester

Continuing with this same excerise...
I have now created the macro plus I have also created a PivotTable for displaying some of the data.

Now, does anyone know what is the best way (if at all possible) to be able to use this new macro/pivotTable on a different (source) data file?

What would be the best way to do this? Would it be possible to 'import' the new source data into the existing file containing the macro, then re-run the macro against the new data? How about the PivotTable? Would that also be regenerated with the new set of input data?

I'm not sure the best way of utilising the macro/table for other sets of data that have the same 'structure' as my original set of data.

Any ideas? Please let me know.
Many thanks
Lester.
10-24-2007, 12:16 AM
TonyJollans

Quote:

Originally Posted by xld

Have you noticed all the KEYGEN posts hitting the forums recently, including on it shouldn't do?

Getting well OT now but yes, interesting, isn't it?
10-24-2007, 01:57 AM
Lester

Quote:

Originally Posted by Lester

Continuing with this same excerise...
I have now created the macro plus I have also created a PivotTable for displaying some of the data.

Now, does anyone know what is the best way (if at all possible) to be able to use this new macro/pivotTable on a different (source) data file?

What would be the best way to do this? Would it be possible to 'import' the new source data into the existing file containing the macro, then re-run the macro against the new data? How about the PivotTable? Would that also be regenerated with the new set of input data?

I'm not sure the best way of utilising the macro/table for other sets of data that have the same 'structure' as my original set of data.

Any ideas? Please let me know.
Many thanks
Lester.

Anyone? Please?
10-24-2007, 02:27 AM
unmarkedhelicopter

Quote:

Originally Posted by Lester

xld...Genial! Funciona muy bien. Tuve que modificarlo un poquito, pero me sirve de mucha ayuda. Cheers.

XLD, (Bob) I think he thinks you are a Chilean, Spanish speaker. I wonder where he picked that up ??? :dunno

I was in the Prada Museum in Madrid and heard a woman order "Dos Beerios" in the Caf? there. I should imagine your spanish is better than that as you have worked in spanish speaking countries ... but how good ?
10-24-2007, 03:13 AM
Lester

Quote:

Originally Posted by unmarkedhelicopter

XLD, (Bob) I think he thinks you are a Chilean, Spanish speaker. I wonder where he picked that up ??? :dunno

It was the Chilean flag and his profile...you guys, what are you like, eh?!
Anyhow, can anyone help me with my question (see my last entry above).