Finding NExt Blank Cell in Range

[Nigel Bennett]

I received an answer to my last question and I have to say
first off, I am alway aprreciative of everybody who takes
the time to answer and I respect and admire your
programming skills.

My problem this time is as follows

I want to check a range (b2 to Z2) and I want to find the
first blank cell, once that cell is found I want it to
check a cell from the menu sheet and if there is something
in the cell insert it in the first blank cell

ie I enter YR 2005 in cell G22 on the menu sheet, then
when I look at sheet 2 it checks the range and when it
comes across the first blank cell it inserts that value

Thanks

Nigel

 

[Tom Ogilvy]

What triggers the macro, entring a value in G22?

If so the right click on the sheet tab of the menu sheet and select view
code. Paste in code like this:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim rng As Range

If Target.Address = "$G$22" Then
Set rng = Worksheets("Sheet2").Range("B2:Z2")
For Each cell In rng
If IsEmpty(cell) Then
cell.Value = Target.Value
Exit For
End If
Next
End If
End Sub

 

[Trevor Shuttleworth]

Nigel

one way:

Sub CopyFromMenu()
Range("B2").End(xlToRight).Offset(0, 1) _
= Sheets("Menu").Range("G22")
End Sub

If G22 is blank it will copy the blank so nothing lost, nothing gained.

Regards

Trevor

 

[Nigel Bennett]

OK Tom that works and I was hoping I could expand on it a
bit

I am using some other code (here it is)which looks at a
range of cells which contains the sheet names in the
workbook and activates each sheet in turn and moves on

Application.ScreenUpdating = True
Dim cell As Range, rng As Range
Dim sh As Worksheet
With Worksheets("Menu")
Set rng = .Range(.Cells(2, 14), .Cells(2, 14).End(xlDown))
End With
For Each cell In rng
Set sh = Worksheets(cell.Value)
Sheets(sh.Name).Activate


Next


How would I incorperate your code into it I tried and
failed miserably

Application.ScreenUpdating = True
Dim cell As Range, rng As Range
Dim sh As Worksheet
With Worksheets("Menu")
Set rng = .Range(.Cells(2, 14), .Cells(2, 14).End(xlDown))
End With
For Each cell In rng
Set sh = Worksheets(cell.Value)
Sheets(sh.Name).Activate
If Target.Address = "$G$22" Then
Set rng = Worksheets("Sheet2").Range("B2:Z2")
For Each cell In rng
If IsEmpty(cell) Then
cell.Value = Target.Value
Exit For
End If
Next
End If


Next

Thanks

Nige

 

[Tom Ogilvy]

I have no idea where that code is being run or what it is supposed to do.
If both pieces are in the change event then just executed them sequentially.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

Application.ScreenUpdating = True
Dim cell As Range, rng As Range
Dim sh As Worksheet
With Worksheets("Menu")
Set rng = .Range(.Cells(2, 14), .Cells(2, 14).End(xlDown))
End With
For Each cell In rng
Set sh = Worksheets(cell.Value)
Sheets(sh.Name).Activate
Next

' --------------
If Target.Address = "$G$22" Then
Set rng = Worksheets("Sheet2").Range("B2:Z2")
For Each cell In rng
If IsEmpty(cell) Then
cell.Value = Target.Value
Exit For
End If
Next
End If
'---------------
End Sub

If you mean integrate them in some way so that where the user makes an
entry determines what sheet to go to
Private Sub Worksheet_Change(ByVal Target As Excel.Range)

Application.ScreenUpdating = True
Dim cell As Range, rng As Range

If Target.column = 7 Then
set sh = cells(Target.row,14).Value.
Set rng = sh.Range("B2:Z2")
For Each cell In rng
If IsEmpty(cell) Then
cell.Value = Target.Value
Exit For
End If
Next
End If
'---------------
End Sub

If you want to do every sheet in the list for an entry made in G22

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

Application.ScreenUpdating = True
Dim cell As Range, rng As Range
Dim rng1 as Range
With Worksheets("Menu")
Set rng1 = .Range(.Cells(2, 14), .Cells(2, 14).End(xlDown))
End With


' --------------
If Target.Address = "$G$22" Then
for each cell in rng1
Set rng = worksheets(cell.value).Range("B2:Z2")
For Each cell In rng
If IsEmpty(cell) Then
cell.Value = Target.Value
Exit For
End If
Next
End If
'---------------
End Sub

 

[Roger PB]

Tom Ogilvy gave a helpful answer regarding a query by Nigel Bennett.

I have been trying to adapt this answer so that when a value is entered in a
particular cell, it is replicated elsewhere in a range.
This works fine as long as I define the cell in a line like
'If Target.Address = "$I$39" Then

However, I want to vary the target address, using variables and loops, so
entered, as a trial attempt

If Target.Address = Cells(10, 39) Then....
or alternatively
If Target.Address = Worksheets("Sheet3").Range(Cells(10, 39)) Then
If Target.Address = Worksheets("Sheet3").Range(.Cells(10, 39)) Then
All produced an error message when I ran the routine.

However,the line
Set rng = Worksheets("Sheet3").Range(Cells(25, 25), Cells(27, 27)) created
no problem.
I cannot determine the correct syntax, or when one needs the dots, and where
they should be put, or when one can safely omit them .

Tom also wrote

What triggers the macro, entering a value....?

If so the right click on the sheet tab of the menu sheet and select view
code. Paste in code like this:

This was a new technique for me.
Normally I record a macro, assigning a keyboard shortcut, then edit the
macro, pasting in the code I have found in the newsgroup.
Since Tom's code runs automatically, I guess there is no need to assign a
shortcut. But I am not clear when one attaches a routine to a worksheet, and
when one puts it in a module. Or how to assign a keyboard shortcut without
using the macro recorder.

Any help would be appreciated. I have two books on Excel VBA programming,
but neither is very helpful on the above issues.

Rogerpb

 

[Alan Beban]

The dots are used in a structure like

With Worksheets("Sheet3")
MsgBox .Cells(10,39).Address
End with

You can see the difference with and without the dot by running the
following when Sheet3 is NOT the active sheet, in which case
Cells(10,39) (without the dot) will refer to a range on the active sheet:

With Worksheets("Sheet3")
Debug.Print Cells(10, 39).Parent.Name, .Cells(10,39).Parent.Name
End With

The Immediate Window will show that the first refers to the active
sheet, the second to Sheet3

One way to avoid the problems with the dots is to use

Set rng=Worksheets("Sheet3").Range("A1")

Then Range(rng(25,25),rng(27,27)) will refer to Y25:AA27 on Sheet3

By the way

Set rng = Worksheets("Sheet3").Range(Cells(25, 25), Cells(27, 27))

creates no problem only if Sheet3 is the active sheet.

Alan Beban

 

[RogerPB]

One way to avoid the problems with the dots is to use

Set rng=Worksheets("Sheet3").Range("A1")

Then Range(rng(25,25),rng(27,27)) will refer to Y25:AA27 on Sheet3

In fact, I am running the macro whilst in sheet 3......Anyway, I modified my routine as follows:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim rng As Range
Dim mc As Integer

'If Target.Address = "$I$39" Then.... was the orignal line
'and was replaced by the lines proposed by Alan above.

Set rng = Worksheets("Sheet3").Range("A1")
If Target.Address = Range(rng(9, 39)) Then


'Worksheets("Sheet3").Range(Cells(1, 1), Cells(9, 9))
mc = Target.Value
Set rng = Worksheets("Sheet3").Range(Cells(25, 25), Cells(27, 27))
For Each cell In rng
rng.Interior.ColorIndex = mc
cell.Value = Target.Value
'Exit For
'End If
Next
End If

End Sub



However, replacing the line
'If Target.Address = "$I$39" Then...
by
Set rng = Worksheets("Sheet3").Range("A1")
If Target.Address = Range(rng(9, 39)) Then

led to the error message "Method 'Range' of object '-Worksheet'
failed.

So I am still looking for an expression identifying I39 by two
variables representing the row and the column

 

[RogerPB]

One way to avoid the problems with the dots is to use

Set rng=Worksheets("Sheet3").Range("A1")

Then Range(rng(25,25),rng(27,27)) will refer to Y25:AA27 on Sheet3

In fact, I am running the macro whilst in sheet 3......Anyway, I modified my routine as follows:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim rng As Range
Dim mc As Integer

'If Target.Address = "$I$39" Then.... was the orignal line
'and was replaced by the lines proposed by Alan above.

Set rng = Worksheets("Sheet3").Range("A1")
If Target.Address = Range(rng(9, 39)) Then


'Worksheets("Sheet3").Range(Cells(1, 1), Cells(9, 9))
mc = Target.Value
Set rng = Worksheets("Sheet3").Range(Cells(25, 25), Cells(27, 27))
For Each cell In rng
rng.Interior.ColorIndex = mc
cell.Value = Target.Value
'Exit For
'End If
Next
End If

End Sub



However, replacing the line
'If Target.Address = "$I$39" Then...
by
Set rng = Worksheets("Sheet3").Range("A1")
If Target.Address = Range(rng(9, 39)) Then

led to the error message "Method 'Range' of object '-Worksheet'
failed.

So I am still looking for an expression identifying I39 by two
variables representing the row and the column

 

[Alan Beban]

After Set rng=Range("A1")
rng(9,39) refers to AM9 and
Range(rng(9,39),rng(9,40)) refers to AM9:AN9

Target.Address is a string; the other side of the statement is a range.

Alan Beban

 

[RogerPB]

After Set rng=Range("A1")
rng(9,39) refers to AM9 and
Range(rng(9,39),rng(9,40)) refers to AM9:AN9

Target.Address is a string; the other side of the statement is a range.

Okay Alan, my mistake was to to equate a string with a range.
And I thought that rng(9,39) referred to I39, not AM9 and that column
references preceded row references. I live and learn.

But having said that, l I am still looking for an expression
identifying the target cell I39 by two variables representing the row
and the column.

In the original routine I tested
'If Target.Address = "$I$39" Then...
did the job, but I am still not clear as to what I should replace it
with.

What I am trying to do is to get the program to respond to my typing a
number into this cell by copying the same number into a block of nine
cells elsewhere on the sheet.

Not by using the string "$I$39",to identify the target cell but,
rather, variables x and y representing its row and column.

By selecting other values for x and y, a different target cell would
be made to send its value to another block, whose location would also
be determined by the values of x and y, with an offset.

Roger PB

 

[Chip Pearson]

Try something like

If Target.Address = Cells(10,39).Address Then


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[RogerPB]

Try something like

If Target.Address = Cells(10,39).Address Then

Thanks Chip, I got that to work.

Can you tell me why I cannot step through the routine with f8 in
debugging mode.? (It was entered in the code window reached by right
clicking the tab "Sheet3").

Roger PB

 

[Chip Pearson]

You can't directly step through a procedure that takes an
arguments. The best way is to put a break point on the first line
of code in the procedure, and then step through the code after
the break point has been hit.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[RogerPB]

'If Target.Address = "$I$39" ThenOn Wed, 16 Mar 2005 14:51:33 -0800,