Multiply range values with Lookup-value?

[Jen]

Hello again,

I am very very novice but Dave Peterson has been showing me how
usefull VBA can be!
Therefore I have another question which I haven't been able Google.

The case:
How can I "run" through a column from A5 eg till the the last value in
that column.

and multiply B5 till the end-row, C5 till the end-row,....till F
with an HLOOKUP-value performed on the A5-cell.

So:
for A5, B5 becomes: the initial B5-value * Hlookup(A5,$B$1:$D$2),2,0),
C5becomes: C5-value * Hlookup(A5,$B$1:$D$2),2,0),...till F5
for A6,B6 becomes: the initial B6-value * Hlookup(A6,$B$1:$D$2),2,0),
C6becomes: C6-value * Hlookup(A6,$B$1:$D$2),2,0).... till F6
....
etc till the end of column A.

The B till F C columns do not need the formulas, only the multiplied
value.

I am sorry for the "messy" explanation, I hope you can help me,
Jen

 

[Guest]

Sub MultiplyValues()
Dim rng1 as Range, rng2 as Range
Dim rng3 as Range, v as Variant
set rng1 = Range("A5",Range("A5").End(xldown))
set rng2 = Range("B12")
for each cell in rng1
v = application.Hlookup(cell,rng2,2,0)
if not iserror(v) then
if isnumeric(v) then
set rng3 = cell.offset(0,1).Resize(1,5)
for each cell1 in rng3
cell1.value = cell1.Value * v
Next cell1
End if
End if
Next cell
End Sub

Test this on a copy of your data.

I assume the notation b6-value means the value of B6 and not B6 minus some
value.

 

[Jen]

Sub MultiplyValues()
Dim rng1 as Range, rng2 as Range
Dim rng3 as Range, v as Variant
set rng1 = Range("A5",Range("A5").End(xldown))
set rng2 = Range("B12")
for each cell in rng1
v = application.Hlookup(cell,rng2,2,0)
if not iserror(v) then
if isnumeric(v) then
set rng3 = cell.offset(0,1).Resize(1,5)
for each cell1 in rng3
cell1.value = cell1.Value * v
Next cell1
End if
End if
Next cell
End Sub

Test this on a copy of your data.

I assume the notation b6-value means the value of B6 and not B6 minus some
value.

--
Regards,
Tom Ogilvy











- Show quoted text -

Hi Tom,

I assume the notation b6-value means the value of B6 and not B6 minus some
value.

No, you understood me there perfectly!
I cannot say much more ...I stand in ore! (if that's how you say it in
English..?)
Simply F A N T A S T I C!

Damn I have to try to properly learn VBA. It has opened my eyes
today!
Once again, thanks a million for your superb code

Jen ))))))))

 

[Jen]

Sub MultiplyValues()
Dim rng1 as Range, rng2 as Range
Dim rng3 as Range, v as Variant
set rng1 = Range("A5",Range("A5").End(xldown))
set rng2 = Range("B12")
for each cell in rng1
v = application.Hlookup(cell,rng2,2,0)
if not iserror(v) then
if isnumeric(v) then
set rng3 = cell.offset(0,1).Resize(1,5)
for each cell1 in rng3
cell1.value = cell1.Value * v
Next cell1
End if
End if
Next cell
End Sub

Test this on a copy of your data.

I assume the notation b6-value means the value of B6 and not B6 minus some
value.

--
Regards,
Tom Ogilvy











- Show quoted text -

Oops just forgot to mention:
I declared "cell" and "cell1" as Variants ... otherwise it bugged.
It works with Variants, but is it the right declaration?

 

[Dave Peterson]

I'm not Tom, but I bet he'd say "even though 'As Variant' will work, it's better
to use 'As Range.'"

ps. You may have noticed that in the excel newsgroups, most people top post.
Yep, it's different from the wild, wild USENet.

 

[Jen]

I'm not Tom, but I bet he'd say "even though 'As Variant' will work, it's better
to use 'As Range.'"

ps. You may have noticed that in the excel newsgroups, most people top post.
Yep, it's different from the wild, wild USENet.

Hi Dave
, thanks for the support again.

What do you mean with "top post"?
Jen

 

[Dave Peterson]

I mean that the replies to messages come at the top of the reply--not the
bottom. I find it easier to just read the top of the message and not have to
scroll down through miles and miles of message text. (Again, this is different
than most newsgroups.)

Since you're posting via google, you can hit the reply button, then hit
ctrl-home to get to the top of that reply box (_I think_???).

ps. If you're going to spend any time in the newsgroups, you may find
connecting directly to the MS News Servers quicker and easier to use.

Saved from a previous post:

If you have Outlook Express installed, try clicking on these links (or copy and
paste into MSIE).

news://msnews.microsoft.com/microsoft.public.excel.setup
news://msnews.microsoft.com/microsoft.public.excel.misc
news://msnews.microsoft.com/microsoft.public.excel.worksheet.functions
news://msnews.microsoft.com/microsoft.public.excel.newusers
news://msnews.microsoft.com/microsoft.public.excel.programming

(and a few more for MSWord)
news://msnews.microsoft.com/microsoft.public.word.docmanagement
news://msnews.microsoft.com/microsoft.public.word.word97vba
news://msnews.microsoft.com/microsoft.public.word.newusers
news://msnews.microsoft.com/microsoft.public.word.pagelayout
news://msnews.microsoft.com/microsoft.public.word.vba.addins
news://msnews.microsoft.com/microsoft.public.word.vba.beginners
news://msnews.microsoft.com/microsoft.public.word.vba.customization
news://msnews.microsoft.com/microsoft.public.word.vba.general
news://msnews.microsoft.com/microsoft.public.word.vba.userforms
news://msnews.microsoft.com/microsoft.public.word.word6-7macros

(You can always connect to more later)

Here are some links that explain it better:

Chip Pearson has some notes written by Leonard Meads at:
http://www.cpearson.com/excel/DirectConnect.htm

David McRitchie's notes at:
http://www.mvps.org/dmcritchie/excel/xlnews.htm
http://www.mvps.org/dmcritchie/excel/oe6.htm
http://www.mvps.org/dmcritchie/excel/oe6nws01.htm

Tushar Mehta's notes at:
http://www.tushar-mehta.com/misc_tutorials/oe_ng/index.htm

And if you're looking for old posts:

Or you can use google (maybe a few hours behind) to search for stuff you've
posted (and find the replies, too)

http://groups.google.com/advanced_group_search
http://groups.google.com/advanced_group_search?q=group:*Excel*&num=100

Ron de Bruin has an excel addin that you may like:
http://www.rondebruin.nl/Google.htm

 

[Jen]

Hi Dave,

Crash-course VBA and Outlook Express in 2 days ...
I haven't read everything up on David McRitchie's site about OE, but
hopefully enough to post this message

Since you're posting via google, you can hit the reply button

Wonder how you could see that I posted via Google but that must be the
"experience" I guess. ... can you tell that actually I am a grumpy old grey
man?

, then hit ctrl-home to get to the top of that reply box (_I think_???).

I'll try later ...this is my first post via OE. Exciting!

Ron de Bruin has an excel addin that you may like:

That's the add-in I was using to to google the Excel newsgroup.

A presto, Jen

 

[Jen.Carllson]

TOP-POSTED via CTRL-HOME

Hi Dave,

Crash-course VBA and Outlook Express in 2 days ...
I haven't read everything up on David McRitchie's site about OE, but
hopefully enough to post this message


Wonder how you could see that I posted via Google but that must be the
"experience" I guess. ... can you tell that actually I am a grumpy old grey
man? >, then hit ctrl-home to get to the top of that reply box (_I think_???).

I'll try later ...this is my first post via OE. Exciting!


That's the add-in I was using to to google the Excel newsgroup.

A presto, Jen


















- Show quoted text -

 

[Dave Peterson]

Thanks for top posting <bg>.

I use an old version of Netscape Communicator to read the newsgroups.

Today your headers look like:

From:
"Jen" <youremailidhere>
References:
<[email protected]>
<[email protected]>
<[email protected]>
<[email protected]>
<[email protected]>
<[email protected]>
Subject: Re: Multiply range values with Lookup-value?
Date: Sat, 2 Jun 2007 09:23:21 +0200
Lines: 118
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.2869


Earlier, your headers looked like:

From: Jen <youremailidhere>
Newsgroups: microsoft.public.excel.programming
Subject: Multiply range values with Lookup-value?
Date: Fri, 01 Jun 2007 10:54:33 -0700
Organization: http://groups.google.com

In OE, you can use:

You can show the properties of a selected message and look at the headers:
File|Properties|Details tab
(View message source to make it easier to read.)

And thanks for top posting (again!).

You're next project is to munge your email address.

Instead of using your real email address that can be read by those spammers, you
can add a few characters to try to make it harder for those spambots to
determine what it actually is:

(e-mail address removed)

is a common way.

 

[Dave Peterson]

Next project--learning to snip!

(No, I don't always do this, either.)

TOP-POSTED via CTRL-HOME

<<snipped>>

 

[Jen]

Hi Dave

Phew, I need to get familiar with this OE...all new to me!.
-Would like to create some rule to see the messages you are posting. As I
find them very instructive
-And then the downloading of messages... it seems I have like 24k messages
which i can all see and read ...but i still need to download them?
It is a bit confusing still.

Eh, ... where did the MS Boys hide my emailaddress in OE? It took me ages to
find it.
But it should be changed now.

My other "homework" you gave me... I'll need more expalantion about that!
Snipped? What on earth is that? And how do i do that?

Jen

 

[Dave Peterson]

I don't use OE for newsgroups enough to be helpful.

But if you don't get a response, take a look at one of the OE newsgroups--or
google to find out how.

And snipped is just cutting the extra stuff out of the message. If the purpose
of the post has changed, then why include the stuff that was already resolved?

Just select the stuff that doesn't need to be there and edit|cut (or hit the
delete key).

I think you can rightclick on the newsgroup and select Catchup and not have to
download any of those new messages. (But it's been a while...)

Hi Dave

Phew, I need to get familiar with this OE...all new to me!.
-Would like to create some rule to see the messages you are posting. As I
find them very instructive
-And then the downloading of messages... it seems I have like 24k messages
which i can all see and read ...but i still need to download them?
It is a bit confusing still.

Eh, ... where did the MS Boys hide my emailaddress in OE? It took me ages to
find it.
But it should be changed now.

My other "homework" you gave me... I'll need more expalantion about that!
Snipped? What on earth is that? And how do i do that?

Jen

<<snipped>>

 

[Jen]

OK