Offset(0, 1) and then Offset(1, 0)?

[ryguy7272]

This is a trick one! I am trying to figure out how to identify blanks in a
range, and then get Excel to give me the ‘Address’ of each blank cell. I got
some great help at this DG last week and my code (below) works, but I am
wondering if I can modify it slightly. Currently, any blanks that are
identified are listed in column AX, and the list starts in AX3 and goes down
one and over zero (rngToPaste.Offset(1, 0)). I am curious to know if I can
get the rngToPaste.Offset to be equal to the row that the blanks are on. For
instance, if I have a blank cell in E3, I want this cell address to be
displayed in AX3, and if I have a blank in F3, I want I want this cell
address to be displayed in AY3, and if I have a blank in G3, I want this cell
address to be displayed in AZ3. Then, if the next blank is in F5, I want
this address to be displayed in AX5. Essentially, I want the cell Addresses
to correspond to the rows than the blanks are on, instead if shifting down
one cell in Column AX each time a blank is found. I assume this is possible.
I am guessing it would be something like: Offset(0, 1) and then Offset(1,
0), but I don't know how to modify the code to do what I want to do. Does
anyone know if this is possible?

My current code is listed below:
Sub FindBlanks()
Dim rngMyRange As Range
Dim rngBlanks As Range
Dim rngToPaste As Range
Dim rng As Range


On Error Resume Next
Set rngMyRange = Union(Range("E3:W330"), Range("Y3:AE330"))
Set rngBlanks = rngMyRange.SpecialCells(xlCellTypeBlanks)
On Error GoTo 0

Set rngToPaste = rngBlanks
If Not rngBlanks Is Nothing Then
Set rngToPaste = Sheets("15.1 Detail - Madeup").Range("AX3")
For Each rng In rngBlanks
rngToPaste.Value = rng.Address
Set rngToPaste = rngToPaste.Offset(1, 0)
Next rng
End If

End Sub


Cordially,
Ryan---

 

[Joel]

Sub FindBlanks()
Dim rngMyRange As Range
Dim rngBlanks As Range
Dim rngToPaste As Range
Dim rng As Range


On Error Resume Next
Set rngMyRange = Union(Range("E3:W330"), Range("Y3:AE330"))
Set rngBlanks = rngMyRange.SpecialCells(xlCellTypeBlanks)
On Error GoTo 0


With Sheets("15.1 Detail - Madeup")
For Each rng In rngBlanks
.Cells(rng.Row, "AX") = rng.Address
Next rng
End With

End Sub

 

[ryguy7272]

Thanks a bunch Joel! This is so close, but I was hoping to get Excel to
paste the results, starting in column AX, and if there are more than one
blank cells founds in each row, then it would place those cells addresses in
AY, AZ, etc., in the same row, shifting over right one cell each time, until
there are no more blanks in that row. Once no more blanks were found in that
row, the offset would move down one row and shift back to Column AX. I
modified the code slightly, but I’m still not seeing the results that I’m
looking for. Below is the code I am using now:

Sub FindBlanks2()
Dim rngMyRange As Range
Dim rngBlanks As Range
Dim rngToPaste As Range
Dim rng As Range
Dim counter As Integer
Set destrange = Range("AX3")
destrange.CurrentRegion.ClearContents

On Error Resume Next
Set rngMyRange = Union(Range("E3:W330"), Range("Y3:AE330"))
Set rngBlanks = rngMyRange.SpecialCells(xlCellTypeBlanks)
On Error GoTo 0

For Each i In destrange
counter = counter + 1
With Sheets("15.1 Detail - Madeup")
For Each rng In rngBlanks
.Cells(rng.Row, counter) = rng.Address
Next rng
End With
Next


End Sub

There must be some way to do an Offset (0, n), when there are more than one
blank cells on one row. How can this be done?

Thanks so much,
Ryan---

 

[Joel]

Sub FindBlanks2()
Dim rngMyRange As Range
Dim rngBlanks As Range
Dim rngToPaste As Range
Dim rng As Range
Dim counter As Integer
Set destrange = Range("AX3")
destrange.CurrentRegion.ClearContents

On Error Resume Next
Set rngMyRange = Union(Range("E3:W330"), Range("Y3:AE330"))
Set rngBlanks = rngMyRange.SpecialCells(xlCellTypeBlanks)
On Error GoTo 0

StartCol = destrange.Column
MaxColOffset = Columns.Count - StartCol
RowOffset = 0
colOffset = 0
With Sheets("15.1 Detail - Madeup")
For Each rng In rngMyRange
If IsEmpty(rng) Then
destrange. _
Offset(RowOffset, colOffset) = rng.Address
If colOffset = MaxColOffset Then
RowOffset = RowOffset + 1
colOffset = 0
Else
colOffset = colOffset + 1
End If
End If
Next rng
End With

End Sub

 

[ryguy7272]

Thanks again Joel! I looked at the code you supplied me with and it seemed
correct to me (but I don’t know this stuff as well as many others). Then I
ran the code and for a split-second I thought this was going to work. After
a moment (my laptop is slow and feeble) I realized it was building the list
of references and offsetting (0, 1) each time. Now, all of my blank cell
addresses are listed in AX3:FH3. I’m going to play with the code a little
and see what I can do here. If you have any other ideas, please advice.

Kind regards,
Ryan---

 

[Joel]

This is the result I got ( mot all columns). don't understand what you
really want.


$E$3 $F$3 $G$3 $H$3 $I$3 $J$3
$V$13 $W$13 $E$14 $F$14 $G$14 $H$14
$T$24 $U$24 $V$24 $W$24 $E$25 $F$25
$R$35 $S$35 $T$35 $U$35 $V$35 $W$35
$P$46 $Q$46 $R$46 $S$46 $T$46 $U$46
$N$57 $O$57 $P$57 $Q$57 $R$57 $S$57

 

[ryguy7272]

Sorry for the confusion Joel. I tried the two samples of code that you gave
me. The first sub is great, as it starts on the row with the first blank.
However, if there is another blank cell in this row, it overwrites the second
blank cell address in the same cell as the first (all in column AX). It
doesn’t shift right one column when the next blank cell in that row is found.
The second sub is great too, as it finds all blanks on each row, but it
seems to place the first blank cell address in AX3, and then shift right one,
and place the second blank cell address in AY3, etc. This is what I want for
the blanks in each row, but when it hits the end of the range of blanks in
that row, I thought it would shift back to column AX, shift down one row, and
begin again. It doesn’t do this on my system (maybe some setting on my Excel
is different from yours; not sure). I know you said it worked for you, and
it seems to do what it is supposed to do, based the output that you posted,
but it doesn’t seem to be working for me. If you have any other ideas,
please post back. Thanks for the assistance.

Kind regards,
Ryan---

 

[Joel]

The only way I can see the 2nd code not working is if Columns.Count doesn't
equal 256. Try replacing Columns.Count with 256 which is the last column in
Excel 2003. I like using Columns.Count because it will change for excel 2007
which has more than the standard 256 columns.

 

[ryguy7272]

Joel, sorry to be a pain in the butt... I played around with your code (the
first sample) a little and came up with this:

Sub FindBlanks()
Dim rngMyRange As Range
Dim rngBlanks As Range
Dim rngToPaste As Range
Dim rng As Range


On Error Resume Next
Set rngMyRange = Union(Range("A1:G30"), Range("J1:M30"))
Set rngBlanks = rngMyRange.SpecialCells(xlCellTypeBlanks)
On Error GoTo 0


With Sheets("Sheet1")
For Each rng In rngBlanks
.Cells(rng.Row, rng.Column) = rng.Address
Next rng
End With

End Sub

There are a few obvious changes (union, range, sheet name, etc.). This
almost does what I want, but the code causes the cell references to be placed
in the blank cells themselves. How can I modify the code to get the cell
references in the column AX, starting in the row of the first blank cell,
shifting right and down as more blanks are found?

Sorry and thanks and sorry,
Ryan---

 

[Joel]

try this

Sub FindBlanks()
Dim rngMyRange As Range
Dim rngBlanks As Range
Dim rngToPaste As Range
Dim rng As Range

Const StartCell = "AX3"


On Error Resume Next
Set rngMyRange = Union(Range("A1:G30"), Range("J1:M30"))
Set rngBlanks = rngMyRange.SpecialCells(xlCellTypeBlanks)
On Error GoTo 0


StartCol = Range(StartCell).Column
MaxColOffset = Columns.Count - StartCol
RowOff = 0
ColOff = 0
With Sheets("Sheet1")
For Each rng In rngBlanks
.Range(StartCell).Offset(Rowoffset:=RowOff,
columnoffset:=ColOff) = _
rng.Address

If colOffset = MaxColOffset Then
RowOff = RowOff + 1
ColOff = 0
Else
ColOff = ColOff + 1
End If
Next rng
End With

End Sub

 

[ryguy7272]

Joel thanks so much for everything! Your last suggestion gave me the same
thing as before (everything shifts right). After many futile attempts to get
the thing working, I came up with this:

Sub FindBlanks()
Dim rngMyRange As Range
Dim rngBlanks As Range
Dim rngToPaste As Range
Dim rng As Range


On Error Resume Next
Set rngMyRange = Union(Range("E3:W330"), Range("Y3:AE330"))
Set rngBlanks = rngMyRange.SpecialCells(xlCellTypeBlanks)
On Error GoTo 0


With Sheets("15.1 Detail - Madeup")
For Each rng In rngBlanks
Range("AX3").Select
Selection.Offset(rng.Row, rng.Column) = rng.Address
Next rng
End With

For Each cell In Range("AX1A340")
If cell.Value = "" Then
cell.Value = cell.End(xlToRight).Value
cell.End(xlToRight).ClearContents
End If
Next

End Sub

It is by no means efficient, and certainly not elegant, but it does do what
I want it to do. Again, I couldn’t have gotten this far without you. Thanks
so much!!! I hope I can help others as much as you have helped me.

Regards,
Ryan---