I would try stepping through the code.

Get everything set up the way you need--maybe on a smaller set of data that
fails.

Then go to the VBE and put your cursor somewhere inside that procedure and hit
F8.

F8 will step through each line of code. So you can see how the code gets run.

You'll want to look closely at this portion (if this is the final code):

For Each cell In RngToSearch
v = Application.HLookup(cell, HLookupRange, 2, 0)
If Not IsError(v) Then
If IsNumeric(v) Then
Set ConvRng _
= cell.Offset(0, OffsetToRight).Resize(1, nrColumnsToSelect)
'I like convrng.cells, but in most cases it won't matter.
'but being explicit is nicer than not
For Each cell1 In ConvRng.Cells
cell1.Value = cell1.Value * v
Next cell1
End If
End If
Next cell

Maybe the value v isn't numeric???

=======
ps. There is no such thing as Final code <vbg.



Jen wrote:

Hi Dave,

I was using the "final" code under the weekend on some test sheets with
great success.
But now on my "live-data" the code does not change a thing ...
I get -still / luckily- all the requests for input but the final
calculation remains like untouched.

As if the lookup-valuein the RngToSearch does not match with any of the
HLOOKUp-values in the HLookUpRng!
If I just click A5=HLookUpValue it turns TRUE though ...
Any thoughts?

Jen

"Dave Peterson" wrote in message
...
(Top posted on purpose <vbg)

First, when you ran your code (with "Option Explicit" at the top, it
wouldn't
run, right?

You declared ConvRange as a range, but later in your code, you used
ConvRng. In
this case, it probably wouldn't have mattered--since you used convrng
consistently after that. But if you had used ConvRange somewhere else and
really wanted to use whatever ConvRng was holding, it may have taken a
while to
find that bug.

By using "Option Explcit", you must declare your variables. It seems like
more
work at the beginning, but you'll soon find the benefits are too great not
to do
this. (intellisense and autocomplete always help me.)

Next, you'll see lots of code posted on these newsgroups that look like:

Dim myObject as someobject 'not real objects--just showing the syntax

set myObject = nothing
on error resume next
'the next line can cause an error, but I know that.
'So Ms. VBA, don't bother blowing up or telling me about it!
set myObject = someobjecthere
'I'm done with the line that could cause the error.
'Ms. VBA, please go back to handling all errors until I tell you
otherwise.
'And that's just the syntax that gives back error handling to excel.
On error goto 0

'Test to see if it was successful:
if myObject is nothing then
'that previous Set failed and what I was looking for doesn't exist.
else
'yep, it was there
end if

========
For example:

dim testwks as worksheet
'...a bunch of code here

set testwks = nothing
on error resume next
set testwks = activeworkbook.worksheets("Jen's Worksheet")
on error goto 0

if testwks is nothing then
msgbox "that worksheet doesn't exist!
else
msgbox testwks.range("a1").value 'just doing something
end if

This is how I changed your code:

Option Explicit
Sub MultiplyValuesCombination()

'I like one line per variable. I find that I can find/fix things
quicker.
'but that's a personal preference only.

Dim myDefaultRng As Range
Dim RngToSearch As Range
Dim HLookupRange As Range
Dim ConvRng As Range
Dim v As Variant
Dim cell As Variant
Dim cell1 As Variant
Dim OffsetToRight As Variant
Dim nrColumnsToSelect As Variant
Dim myDefaultHLOOKUPRng As Range

With ActiveSheet
Set myDefaultRng = .Range("A5", .Cells(.Rows.Count, "A").End(xlUp))
End With

Set RngToSearch = Nothing
On Error Resume Next
Set RngToSearch = Application.InputBox _
("Where is the range to apply the search on?", _
Default:=myDefaultRng.Address(external:=True), _
Type:=8)
On Error GoTo 0
If RngToSearch Is Nothing Then
Exit Sub 'user hit cancel
End If


OffsetToRight = Application.InputBox _
("Start selection how many columns to the
Right?", _
Default:=4, Type:=1)
If OffsetToRight = False Then
Exit Sub 'user hit cancel
End If

nrColumnsToSelect = Application.InputBox _
("HOW MANY Columns to select?", _
Default:=5, Type:=1)
If nrColumnsToSelect = False Then
Exit Sub 'user hit cancel
End If

With RngToSearch
Set RngToSearch = Intersect(.Areas(1).EntireRow,
.Parent.Range("A:A"))
End With

Set myDefaultHLOOKUPRng = Nothing
On Error Resume Next
'I added the activesheet here!
Set myDefaultHLOOKUPRng = ActiveSheet.Range("conv")
On Error GoTo 0

If myDefaultHLOOKUPRng Is Nothing Then
Exit Sub
End If

Set HLookupRange = Application.InputBox _
("Where is the range with the" & _
" CONVERSION RATIOS (Max 2rows & ratio in 2nd!)?", _
Default:=myDefaultHLOOKUPRng.Address(external:=Tru e), Type:=8)

If HLookupRange Is Nothing Then
Exit Sub 'user hit cancel
End If

For Each cell In RngToSearch
v = Application.HLookup(cell, HLookupRange, 2, 0)
If Not IsError(v) Then
If IsNumeric(v) Then
Set ConvRng _
= cell.Offset(0, OffsetToRight).Resize(1, nrColumnsToSelect)
'I like convrng.cells, but in most cases it won't matter.
'but being explicit is nicer than not
For Each cell1 In ConvRng.Cells
cell1.Value = cell1.Value * v
Next cell1
End If
End If
Next cell
End Sub

=========
Right now you're asking the user 4 questions. That's stretching the
limits of
clicking ok for me.

Imagine if you had to answer each of the settings under File|Page setup by
using
an inputbox. The way you'd clean this kind of stuff up is to design a
userform
that can ask those same questions. In fact, for the questions that return
numbers, you could use a scrollbar or spinner or even a textbox if you
wanted.

Someday (probably not day one!), you'll want to learn how to do this.

Check Debra Dalgleish's site:
http://contextures.com/xlUserForm01.html

and these articles by Peter:
http://msdn2.microsoft.com/en-us/lib...ffice.10).aspx
http://msdn2.microsoft.com/en-us/lib...ffice.10).aspx

And when you think you want to look at books...

Debra Dalgleish has a list of books at her site:
http://www.contextures.com/xlbooks.html

John Walkenbach's is a nice one to start with.

See if you can find them in your local bookstore/internet site and you can
choose what one you like best.




Jen wrote:
<<snipped

Hi Dave,

-THANK you SOO much for the explanation. Sounds "logic", but it is
deviously cool!!!

-I am toying around with the VBA on some "practise" sheets, where I
practise on column A. I will apply it in the end on column G in my
"real live" file.

Anyway, I "advanced" a bit further on your code and this is my result:

Sub MultiplyValuesCombination()
Dim myDefaultRng As Range
Dim RngToSearch As Range, HLookupRange As Range
Dim ConvRange As Range, v As Variant
Dim cell As Variant
Dim cell1 As Variant
Dim OffsetToRight As Variant
Dim nrColumnsToSelect As Variant
Dim myDefaultHLOOKUPRng As Range

With ActiveSheet
Set myDefaultRng = .Range("A5", .Cells(.Rows.Count,
"A").End(xlUp))
End With

Set RngToSearch = Nothing
On Error Resume Next

Set RngToSearch = Application.InputBox _
("Where is the range to apply the search on?",
_
Default:=myDefaultRng.Address(external:=True),
Type:=8)
On Error GoTo 0
If RngToSearch Is Nothing Then
Exit Sub 'user hit cancel
End If

OffsetToRight = Application.InputBox _
("Start selection how many columns to the
Right?", _
Default:=4, Type:=1)
On Error GoTo 0
If OffsetToRight = False Then
Exit Sub 'user hit cancel
End If

nrColumnsToSelect = Application.InputBox _
("HOW MANY Columns to select?", _
Default:=5, Type:=1)
On Error GoTo 0
If nrColumnsToSelect = False Then
Exit Sub 'user hit cancel
End If

'you may not want this portion
With RngToSearch
Set RngToSearch =
Intersect(.Areas(1).EntireRow, .Parent.Range("A:A"))
End With

'MsgBox RngToSearch.Address(external:=True)

'On Error Resume Next
Set myDefaultHLOOKUPRng = Range("conv")
Set HLookupRange = Application.InputBox _
("Where is the range with the CONVERSION
RATIOS (Max 2rows & ratio in 2nd!)?", _

Default:=myDefaultHLOOKUPRng.Address(external:=Tru e), Type:=8)
On Error GoTo 0
If HLookupRange Is Nothing Then
Exit Sub 'user hit cancel
End If

For Each cell In RngToSearch
v = Application.HLookup(cell, HLookupRange, 2, 0)
If Not IsError(v) Then
If IsNumeric(v) Then
Set ConvRng = cell.Offset(0, OffsetToRight).Resize(1,
nrColumnsToSelect)
For Each cell1 In ConvRng
cell1.Value = cell1.Value * v
Next cell1
End If
End If
Next cell
End Sub

I have a question related with this part:
Set myDefaultHLOOKUPRng = Range("conv")
Set HLookupRange = Application.InputBox _
("Where is the range with the CONVERSION
RATIOS (Max 2rows & ratio in 2nd!)?", _

Default:=myDefaultHLOOKUPRng.Address(external:=Tru e), Type:=8)
On Error GoTo 0
If HLookupRange Is Nothing Then
Exit Sub 'user hit cancel
End If

1.
Normally the ranged Name "conv" DOES exist ... but in case it does NOT
I would just like to select the range but now it errs out!
If I put an "On error resume next" above it, it will just exit the
sub ... but I would like that I can still select my range although the
"conv" name does not exist.

2.
I have repeated plenty of code blocks. I assume that could be written
in a neater way ...?
(Well, "for a first day in the office"... ;) )

The "On error goto 0" are all over the place eg. ... do I need them?
what it means?

Jen, first day on VBA :)

--

Dave Peterson

--

Dave Peterson