Trouble with 2 range definitions ...

[Celt]

All,

Thanks in advance for any help offered!

In the code below, I have a bunch of ranges defined and they all wor
great until "rng3" and "rng4". I won;t know where 2 of these range
will start, hence the use of "icol and jcol".

Set rng = .Range(.Cells(4, 4), _
.Cells(4, 256).End(xlToLeft))
Set rng2A = rng.Find(What:="Code", After:=rng(1), _
LookIn:=xlValues, _
LookAt:=xlPart, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, _
MatchCase:=True)
icol = rng2A.Column

Set rng1 = .Range(.Cells(6, 3), _
.Cells(Rows.Count, 3).End(xlUp))
Set rng2 = .Range(.Cells(6, icol), _
.Cells(Rows.Count, icol).End(xlUp))
Set rng3A = rng.Find(What:="Detailed Description", After:=rng(1), _
LookIn:=xlValues, _
LookAt:=xlPart, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, _
MatchCase:=True)
jcol = rng3A.Column
Set rng3 = .Range(.Cells(cell.Row, 4), .Cells(cell.Row, (jcol - 1)))
Set rng4 = .Range(.Cells(cellA.Row, (cellA.Column + 1))
.Cells(cellA.Row, Cells(Columns.Count).End(xlToLeft)))
End With

In rng3 I am trying to say "start at column 4 on the current cell ro
and extend right to 1 before my unknown column".

In rng4 I am trying to say "start one column to the right of th
current cell and on the same row, extend right as many column as ar
used".

I am sure I have something completely backwards. Can anyone straighte
me out?

Thanks

 

[Jim Thomlinson]

What are Cell and CellA. I am assuming range objects? Where are they defined
and are they valid when these lines of code execute...

Set rng3 = .Range(.Cells(cell.Row, 4), .Cells(cell.Row, (jcol - 1)))
Set rng4 = .Range(.Cells(cellA.Row, (cellA.Column + 1)),

 

[Toppers]

Try:

Set rng3 = .Range(.Cells(ActiveCell.Row, 4), .Cells(ActiveCell.Row, (jcol -
1)))
Set rng4 = .Range(.Cells(ActiveCell.Row, (ActiveCell.Column + 1)),
..Cells(ActiveCell.Row, .Cells(Columns.Count).End(xlToLeft).Column))

 

[Celt]

Hi Jim,
I just realized I had those two lines in the wrong place (I am still
getting my feet wet with VB). Sorry for the confusion.

Yes they are range objects. The macro runs up until the first instance
of ".Cells" when I Set rng3, then I get an "Invaild ir unqualified
reference" message.

Here is my whole macro.

Sub BlankNums()
Dim rng As Range, rng1 As Range
Dim rng2 As Range, rng2A As Range
Dim rng3 As Range, rng3A As Range
Dim cell As Range, cellA As Range

Sheets("Input 502 & 504").Select
With Worksheets("Input 502 & 504")
ActiveSheet.UsedRange
Set rng = .Range(.Cells(4, 4), _
..Cells(4, 256).End(xlToLeft))
Set rng2A = rng.Find(What:="Code", After:=rng(1), _
LookIn:=xlValues, _
LookAt:=xlPart, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, _
MatchCase:=True)
icol = rng2A.Column

Set rng1 = .Range(.Cells(6, 3), _
..Cells(Rows.Count, 3).End(xlUp))
Set rng2 = .Range(.Cells(6, icol), _
..Cells(Rows.Count, icol).End(xlUp))
Set rng3A = rng.Find(What:="Detailed Description", After:=rng(1), _
LookIn:=xlValues, _
LookAt:=xlPart, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, _
MatchCase:=True)
jcol = rng3A.Column


End With
For Each cell In rng1
If IsEmpty(cell) Then
Set rng3 = .Range(.Cells(cell.Row, 4), .Cells(cell.Row, (jcol - 1)))
If Not IsEmpty(rng3) Then
cell.Interior.ColorIndex = 6
End If
End If
For Each cellA In rng2

If IsEmpty(cellA) Then
Set rng4 = .Range(.Cells(cellA.Row, (cellA.Column + 1)),
..Cells(cellA.Row, Cells(Columns.Count).End(xlToLeft)))
If Not IsEmpty(rng4) Then
cellA.Interior.ColorIndex = 6
End If
End If
If cell = "Code" Then
cell.Interior.ColorIndex = xlNone
End If
Next
Next
End Sub

 

[Jim Thomlinson]

Give this a try... I declared the rest of your varaibles and objects and
moved the end with to the very bottom of the code (that is why you are
getting the unqualified reference error)... The . is not referenced by the
with statement any longer. Also be sure to add Option Explicit to the top of
your code modules to require variable declarations. Check out this link...

http://www.cpearson.com/excel/DeclaringVariables.htm

Option Explicit

Sub BlankNums()
Dim rng As Range, rng1 As Range
Dim rng2 As Range, rng2A As Range
Dim rng3 As Range, rng3A As Range
Dim rng4 As Range
Dim cell As Range, cellA As Range
Dim icol As Integer, jcol As Integer

Sheets("Input 502 & 504").Select
With Worksheets("Input 502 & 504")
ActiveSheet.UsedRange
Set rng = .Range(.Cells(4, 4), _
..Cells(4, 256).End(xlToLeft))
Set rng2A = rng.Find(What:="Code", After:=rng(1), _
LookIn:=xlValues, _
LookAt:=xlPart, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, _
MatchCase:=True)
icol = rng2A.Column

Set rng1 = .Range(.Cells(6, 3), _
..Cells(Rows.Count, 3).End(xlUp))
Set rng2 = .Range(.Cells(6, icol), _
..Cells(Rows.Count, icol).End(xlUp))
Set rng3A = rng.Find(What:="Detailed Description", After:=rng(1), _
LookIn:=xlValues, _
LookAt:=xlPart, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, _
MatchCase:=True)
jcol = rng3A.Column


For Each cell In rng1
If IsEmpty(cell) Then
Set rng3 = Range(Cells(cell.Row, 4), .Cells(cell.Row, (jcol - 1)))
If Not IsEmpty(rng3) Then
cell.Interior.ColorIndex = 6
End If
End If
For Each cellA In rng2

If IsEmpty(cellA) Then
Set rng4 = .Range(.Cells(cellA.Row, (cellA.Column + 1)), .Cells(cellA.Row,
Cells(Columns.Count).End(xlToLeft)))
If Not IsEmpty(rng4) Then
cellA.Interior.ColorIndex = 6
End If
End If
If cell = "Code" Then
cell.Interior.ColorIndex = xlNone
End If
Next
Next
End With

End Sub

 

[Celt]

Hi Jim,

I put your corrections in, and added the "Option Explicit". The cod
ran up to where I Set rng4 and then returned a "Runtime Error 13 : Typ
mismatch". The editor highlights the entire "Set rng4 =" line yello
and when I click definition, it shoots me back up to the "Dim rng4 A
Range" line.


Any ideas

 

[Jim Thomlinson]

You just need to add .Column I think...

Set rng4 = .Range(.Cells(cellA.Row, (cellA.Column + 1)), .Cells(cellA.Row,
Cells(Columns.Count).End(xlToLeft).column))

 

[Celt]

That did it.

Thanks very much Jim!