Set a Range using Cells(3,12) and not .Range("A4:A&quot

gidi_up

New Member
Joined
Jan 4, 2006
Messages
6
Hello,
This is most likely simpler that what I am killing myself over, so thanks in advanced for assisting the code challenged.

It is a fixed Range is from Row 3 to 1002, but need to incroment the Column by 2, below is what I have but getting error on the 'Set CheckColumn...", which I am just trying to rationalize the Range("A4:A" & Range("A504") to the Cells method.

Code:
Sub FindProdandSam()
Dim CheckColumn As Range, ProductList As Range, SampleList As Range

Set ProductList = Sheets("Products").Range("A4:A" & Range("A504").End(xlUp).Row)
Set SampleList = Sheets("Products").Range("I4:I" & Range("I104").End(xlUp).Row)

i = 10
For i = i To 200
ThisCol = i + 2
Set CheckColumn = Sheets("Contact").Cells(3, ThisCol) & Cells(3, 1002).End(xlUp).Row

SummaryRow = 3
'Check for matches then get Quanity-------------------------------------------------
For Each SampleNum In SampleList
GetRow = 2
QtySample = 0
SummaryRow = SummaryRow + 1
   For Each FindSample In CheckColumn
         GetRow = GetRow + 1 'This is the row counter to get Column B, Row X
                If SampleNum = FindSample Then
                   GetQty = Sheets("Contact").Range("K" & GetRow)
                   QtySample = QtySample + GetQty
                   Cells(SummaryRow, "O") = QtySample
                End If
   Next FindSample
Next SampleNum

Next i
End Sub

EDIT: added code tags - Moderator
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
i don't know the answer, a hint i found the hard way
lots of the pros on here won't reply unless you inbedded it with the code brackets if using code
 
Upvote 0
Hi

Cells used on its own will only return a single cell - you need to use it with Range to return a range of more than one cell. Also, you seem to be concatenating which isn't going to work - the ampersand is a string operator. Possibly:

Code:
With Sheets("Contact")
   Set CheckColumn = .Range(.Cells(3, ThisCol),.Cells(1002, ThisCol).End(xlUp))

altho this assumes that C1002 is always empty - hence it might be a lot safer to use:

Code:
With Sheets("Contact")
   Set CheckColumn = .Range(.Cells(3, ThisCol),.Cells(Rows.Count,ThisCol ).End(xlUp))
 
Upvote 0
Welcome to the Board!

I think you'd be better off using the Find Method. There's a good example in the helpfile.

As it is now your code is pretty convoluted, especially with all those undeclared variables. Can you clarify what you're trying to accomplish?

As for the Cells reference it's Cells(Rows, Columns), so you're trying to use it backwards, and the 1002 will fail (unless you have Office 2007).


Hope that helps,

Smitty
 
Upvote 0

Forum statistics

Threads
1,213,484
Messages
6,113,927
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top