Trouble with 2 range definitions ...

**Celt** · 04-03-2006, 03:10 PM

All,

Thanks in advance for any help offered!

In the code below, I have a bunch of ranges defined and they all work great until "rng3" and "rng4". I won;t know where 2 of these ranges 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 row and extend right to 1 before my unknown column".

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

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

Thanks!

Jim Thomlinson · 04-03-2006, 03:40 PM

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)),

--
HTH...

Jim Thomlinson

"Celt" wrote:

>
> All,
>
> Thanks in advance for any help offered!
>
> In the code below, I have a bunch of ranges defined and they all work
> great until "rng3" and "rng4". I won;t know where 2 of these ranges
> 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 row
> and extend right to 1 before my unknown column".
>
> In rng4 I am trying to say "start one column to the right of the
> current cell and on the same row, extend right as many column as are
> used".
>
> I am sure I have something completely backwards. Can anyone straighten
> me out?
>
> Thanks!
>
>
> --
> Celt
> ------------------------------------------------------------------------
> Celt's Profile: http://www.excelforum.com/member.php...o&userid=19413
> View this thread: http://www.excelforum.com/showthread...hreadid=529338
>
>

Toppers · 04-03-2006, 03:40 PM

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" wrote:

>
> All,
>
> Thanks in advance for any help offered!
>
> In the code below, I have a bunch of ranges defined and they all work
> great until "rng3" and "rng4". I won;t know where 2 of these ranges
> 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 row
> and extend right to 1 before my unknown column".
>
> In rng4 I am trying to say "start one column to the right of the
> current cell and on the same row, extend right as many column as are
> used".
>
> I am sure I have something completely backwards. Can anyone straighten
> me out?
>
> Thanks!
>
>
> --
> Celt
> ------------------------------------------------------------------------
> Celt's Profile: http://www.excelforum.com/member.php...o&userid=19413
> View this thread: http://www.excelforum.com/showthread...hreadid=529338
>
>

**Celt** · 04-03-2006, 04:30 PM

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 · 04-03-2006, 07:30 PM

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

--
HTH...

Jim Thomlinson

"Celt" wrote:

>
> 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
>
>
> --
> Celt
> ------------------------------------------------------------------------
> Celt's Profile: http://www.excelforum.com/member.php...o&userid=19413
> View this thread: http://www.excelforum.com/showthread...hreadid=529338
>
>

**Celt** · 04-04-2006, 11:13 AM

Hi Jim,

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

Any ideas?

Jim Thomlinson · 04-04-2006, 02:10 PM

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))

--
HTH...

Jim Thomlinson

"Celt" wrote:

>
> Hi Jim,
>
> I put your corrections in, and added the "Option Explicit". The code
> ran up to where I Set rng4 and then returned a "Runtime Error 13 : Type
> mismatch". The editor highlights the entire "Set rng4 =" line yellow
> and when I click definition, it shoots me back up to the "Dim rng4 As
> Range" line.
>
>
> Any ideas?
>
>
> --
> Celt
> ------------------------------------------------------------------------
> Celt's Profile: http://www.excelforum.com/member.php...o&userid=19413
> View this thread: http://www.excelforum.com/showthread...hreadid=529338
>
>

Jim Thomlinson · 04-04-2006, 04:10 PM

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))

--
HTH...

Jim Thomlinson

"Celt" wrote:

>
> Hi Jim,
>
> I put your corrections in, and added the "Option Explicit". The code
> ran up to where I Set rng4 and then returned a "Runtime Error 13 : Type
> mismatch". The editor highlights the entire "Set rng4 =" line yellow
> and when I click definition, it shoots me back up to the "Dim rng4 As
> Range" line.
>
>
> Any ideas?
>
>
> --
> Celt
> ------------------------------------------------------------------------
> Celt's Profile: http://www.excelforum.com/member.php...o&userid=19413
> View this thread: http://www.excelforum.com/showthread...hreadid=529338
>
>