(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:=True), 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/li...ft.com/en-us/library/aa155610(office.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>>
--
Dave Peterson- Hide quoted text -
- Show quoted text -
)