ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Trouble with 2 range definitions ... (https://www.excelbanter.com/excel-programming/357897-trouble-2-range-definitions.html)

Celt[_39_]
Trouble with 2 range definitions ...
 

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

--
Cel
-----------------------------------------------------------------------
Celt's Profile: http://www.excelforum.com/member.php...fo&userid=1941
View this thread: http://www.excelforum.com/showthread.php?threadid=52933

Jim Thomlinson
Trouble with 2 range definitions ...
 
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
Trouble with 2 range definitions ...
 
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[_40_]
Trouble with 2 range definitions ...
 

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

Jim Thomlinson
Trouble with 2 range definitions ...
 
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[_41_]
Trouble with 2 range definitions ...
 

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

--
Cel
-----------------------------------------------------------------------
Celt's Profile: http://www.excelforum.com/member.php...fo&userid=1941
View this thread: http://www.excelforum.com/showthread.php?threadid=52933

Jim Thomlinson
Trouble with 2 range definitions ...
 
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

Celt[_42_]
Trouble with 2 range definitions ...
 

That did it.

Thanks very much Jim!

--
Cel
-----------------------------------------------------------------------
Celt's Profile: http://www.excelforum.com/member.php...fo&userid=1941
View this thread: http://www.excelforum.com/showthread.php?threadid=52933


All times are GMT +1. The time now is 04:01 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com