I wish to establish if an offset RANGE contains text.

so, activecell and the cell below can be merged, and then populated.

something like....

if Range(ActiveCell.Offset(0, 0), ActiveCell.Offset(1, 0)).value is
nothing then
my macro
else
my macro
end if

Can anybody please help?

if isempty(activecell.offset(1,0).value) then
'single cell under the activecell

or maybe...

if application.counta(activecell.resize(2,1)) 0 then
'at least one of the cells (activecell or the one below it) has something
'in it


Mik wrote:

I wish to establish if an offset RANGE contains text.

so, activecell and the cell below can be merged, and then populated.

something like....

if Range(ActiveCell.Offset(0, 0), ActiveCell.Offset(1, 0)).value is
nothing then
my macro
else
my macro
end if

Can anybody please help?

--

Dave Peterson

On Apr 27, 11:01*pm, Dave Peterson wrote:
if isempty(activecell.offset(1,0).value) then
* 'single cell under the activecell

or maybe...

if application.counta(activecell.resize(2,1)) 0 then
* *'at least one of the cells (activecell or the one below it) has something
* *'in it





Mik wrote:

I wish to establish if an offset RANGE contains text.

so, activecell and the cell below can be merged, and then populated.

something like....

if Range(ActiveCell.Offset(0, 0), ActiveCell.Offset(1, 0)).value is
nothing then
my macro
else
my macro
end if

Can anybody please help?

--

Dave Peterson- Hide quoted text -

- Show quoted text -

Dave,

Thanks for your responce, however, I am having problems trying to get
it working.

What I am trying to achieve is...
Cells have pulldown menu's where the user can select 'Type 2' or 'Type
3' appointments and so on...
Type 2 will occupy two cells
Type 3 will occupy three cells etc..

If a space of only 2 cells are available say 'A3:A4', because A5 may
be occupied, then the user can not enter a Type 3 appointment, as
there is insufficient space (only 2 cells available, not 3).
So, your code .... If Application.CountA(ActiveCell.Resize(2, 1))
0 Then ...... should determine if sufficient space is available.
Hope this makes sense.

My code (shortened, as there are 7 Case scenarios) is as follows...

Positioned within 'This Workbook'

Private Sub Workbook_sheetChange(ByVal Sh As Object, ByVal Target As
Range)

Select Case ActiveCell
Case "Type 2"
If Application.CountA(ActiveCell.Resize(2, 1)) 0 Then
MsgBox ("Insufficient space")
Else
Range(ActiveCell.Offset(0, 0), ActiveCell.Offset(1,
0)).Select
MsgBox ("OK to continue")
End If

Case "Type 3"
If Application.CountA(ActiveCell.Resize(3, 1)) 0 Then
MsgBox ("Insufficient space")
Else
Range(ActiveCell.Offset(0, 0), ActiveCell.Offset(2,
0)).Select
MsgBox ("OK to continue")
End If

End Select
End Sub

Hope you can assist.
Thanks in advance.
Mik

First, I would guess that you really don't want this procedure to run for each
worksheet in the workbook. (I could be wrong, though!)

Second, it's a pretty good idea to specify the range that you want to monitor.
I used A2:J2 in my code, but it could be any range you want.

Third, these events have parms passed to them. Sh and Target in the version you
posted. Sh is the sheet that has the change. Target is the cell(s) (could be
more than one!) that changed.

(If you wanted this same routine for (say) 10 out of 15 worksheets in the
workbook, you could use Sh to find out if the sheet should be processed. Post
back with more details if that's the case.)

Fourth, since you did a nice job naming the types (Type 2, Type 3, ..., Type 8),
you can extract that last number and use it in the code to find the size of the
range.

So if A2 held type 5, then you'd drop down to A3 and look at 5 cells.
Target.offset(1,0) is dropping down one.
Target.offset(1,0).resize(5,1) makes it 5 rows by 1 column.

So if all that sounds reasonable...

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim myStr As String
Dim HowMany As String
Dim RngToCheck As Range

Set Target = Target.Cells(1) 'just a single cell!

'just check the changes in a specific range
'I used A2:J2, change this to match what you need
If Intersect(Target, Me.Range("A2:J2")) Is Nothing Then
Exit Sub
End If

myStr = UCase(Target.Value)

If myStr Like UCase("TYPE *") Then
'ok to continue
Else
'bad type # (this shouldn't happen, right???)
Exit Sub
End If

HowMany = Mid(myStr, 6) 'ignore the first 5 characters!

If IsNumeric(HowMany) = False Then
'not a valid option!
Exit Sub
End If

Set RngToCheck = Target.Offset(1, 0).Resize(CLng(HowMany), 1)

If Application.CountA(RngToCheck) 0 Then
MsgBox "Insufficient space!"
Else
MsgBox "Ok to continue" 'do you really want that?
End If

End Sub


Mik wrote:

On Apr 27, 11:01 pm, Dave Peterson wrote:
if isempty(activecell.offset(1,0).value) then
'single cell under the activecell

or maybe...

if application.counta(activecell.resize(2,1)) 0 then
'at least one of the cells (activecell or the one below it) has something
'in it





Mik wrote:

I wish to establish if an offset RANGE contains text.

so, activecell and the cell below can be merged, and then populated.

something like....

if Range(ActiveCell.Offset(0, 0), ActiveCell.Offset(1, 0)).value is
nothing then
my macro
else
my macro
end if

Can anybody please help?

--

Dave Peterson- Hide quoted text -

- Show quoted text -

Dave,

Thanks for your responce, however, I am having problems trying to get
it working.

What I am trying to achieve is...
Cells have pulldown menu's where the user can select 'Type 2' or 'Type
3' appointments and so on...
Type 2 will occupy two cells
Type 3 will occupy three cells etc..

If a space of only 2 cells are available say 'A3:A4', because A5 may
be occupied, then the user can not enter a Type 3 appointment, as
there is insufficient space (only 2 cells available, not 3).
So, your code .... If Application.CountA(ActiveCell.Resize(2, 1))
0 Then ...... should determine if sufficient space is available.
Hope this makes sense.

My code (shortened, as there are 7 Case scenarios) is as follows...

Positioned within 'This Workbook'

Private Sub Workbook_sheetChange(ByVal Sh As Object, ByVal Target As
Range)

Select Case ActiveCell
Case "Type 2"
If Application.CountA(ActiveCell.Resize(2, 1)) 0 Then
MsgBox ("Insufficient space")
Else
Range(ActiveCell.Offset(0, 0), ActiveCell.Offset(1,
0)).Select
MsgBox ("OK to continue")
End If

Case "Type 3"
If Application.CountA(ActiveCell.Resize(3, 1)) 0 Then
MsgBox ("Insufficient space")
Else
Range(ActiveCell.Offset(0, 0), ActiveCell.Offset(2,
0)).Select
MsgBox ("OK to continue")
End If

End Select
End Sub

Hope you can assist.
Thanks in advance.
Mik

--

Dave Peterson

On Apr 28, 9:10*pm, Dave Peterson wrote:
First, I would guess that you really don't want this procedure to run for each
worksheet in the workbook. *(I could be wrong, though!)

Second, it's a pretty good idea to specify the range that you want to monitor.
I used A2:J2 in my code, but it could be any range you want.

Third, these events have parms passed to them. *Sh and Target in the version you
posted. *Sh is the sheet that has the change. *Target is the cell(s) (could be
more than one!) that changed.

(If you wanted this same routine for (say) 10 out of 15 worksheets in the
workbook, you could use Sh to find out if the sheet should be processed. *Post
back with more details if that's the case.)

Fourth, since you did a nice job naming the types (Type 2, Type 3, ..., Type 8),
you can extract that last number and use it in the code to find the size of the
range.

So if A2 held type 5, then you'd drop down to A3 and look at 5 cells.
Target.offset(1,0) is dropping down one.
Target.offset(1,0).resize(5,1) makes it 5 rows by 1 column.

So if all that sounds reasonable...

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

* * Dim myStr As String
* * Dim HowMany As String
* * Dim RngToCheck As Range

* * Set Target = Target.Cells(1) 'just a single cell!

* * 'just check the changes in a specific range
* * 'I used A2:J2, change this to match what you need
* * If Intersect(Target, Me.Range("A2:J2")) Is Nothing Then
* * * * Exit Sub
* * End If

* * myStr = UCase(Target.Value)

* * If myStr Like UCase("TYPE *") Then
* * * * 'ok to continue
* * Else
* * * * 'bad type # (this shouldn't happen, right???)
* * * * Exit Sub
* * End If

* * HowMany = Mid(myStr, 6) 'ignore the first 5 characters!

* * If IsNumeric(HowMany) = False Then
* * * * 'not a valid option!
* * * * Exit Sub
* * End If

* * Set RngToCheck = Target.Offset(1, 0).Resize(CLng(HowMany), 1)

* * If Application.CountA(RngToCheck) 0 Then
* * * * MsgBox "Insufficient space!"
* * Else
* * * * MsgBox "Ok to continue" 'do you really want that?
* * End If

End Sub





Mik wrote:

On Apr 27, 11:01 pm, Dave Peterson wrote:
if isempty(activecell.offset(1,0).value) then
* 'single cell under the activecell

or maybe...

if application.counta(activecell.resize(2,1)) 0 then
* *'at least one of the cells (activecell or the one below it) has something
* *'in it

Mik wrote:

I wish to establish if an offset RANGE contains text.

so, activecell and the cell below can be merged, and then populated..

something like....

if Range(ActiveCell.Offset(0, 0), ActiveCell.Offset(1, 0)).value is
nothing then
my macro
else
my macro
end if

Can anybody please help?

--

Dave Peterson- Hide quoted text -

- Show quoted text -

Dave,

Thanks for your responce, however, I am having problems trying to get
it working.

What I am trying to achieve is...
Cells have pulldown menu's where the user can select 'Type 2' or 'Type
3' appointments and so on...
Type 2 will occupy two cells
Type 3 will occupy three cells etc..

If a space of only 2 cells are available say 'A3:A4', because A5 may
be occupied, then the user can not enter a Type 3 appointment, as
there is insufficient space (only 2 cells available, not 3).
So, your code .... * If Application.CountA(ActiveCell.Resize(2, 1))
0 Then ...... should determine if sufficient space is available.
Hope this makes sense.

My code (shortened, as there are 7 Case scenarios) is as follows...

Positioned within 'This Workbook'

Private Sub Workbook_sheetChange(ByVal Sh As Object, ByVal Target As
Range)

Select Case ActiveCell
Case "Type 2"
* * * If Application.CountA(ActiveCell.Resize(2, 1)) 0 Then
* * * * * *MsgBox ("Insufficient space")
* * * Else
* * * * * *Range(ActiveCell.Offset(0, 0), ActiveCell.Offset(1,
0)).Select
* * * * * *MsgBox ("OK to continue")
* * * End If

Case "Type 3"
* * * If Application.CountA(ActiveCell.Resize(3, 1)) 0 Then
* * * * * *MsgBox ("Insufficient space")
* * * Else
* * * * * *Range(ActiveCell.Offset(0, 0), ActiveCell.Offset(2,
0)).Select
* * * * * *MsgBox ("OK to continue")
* * * End If

End Select
End Sub

Hope you can assist.
Thanks in advance.
Mik

--

Dave Peterson- Hide quoted text -

- Show quoted text -

Thanks for the reply.
I must inform you that I am a newbie, and I do not entirely understand
your code.

Answers to your initial questions...

1) I do not wish to run this code on all workshsheets. Just Sheet 1.
2) The range should be limited to B2:F36.
3) Understood.
4) The types could be kept with the name Type 1, Type 2 etc...,
however I may wish to change these names to actual appointment names,
rather than types.

I have added the code, and changed the range to B2:F6, but can't see
where i am going wrong.

Would it be possible to send you the Excel file? Or is this forbidden
on this site?
If I can send it, how is best ?

Mik

No thanks to the file.

Did you delete the code in the ThisWorkbook module?
Did you add the code to the Sheet1 module (rightclick on the worksheet tab and
select view code). Paste into that code window.

You allow "Type #" in all 175 cells (all of B2:F36)???? That seems kind of
weird to me.

If you change the values, then the code won't work. The modification could be
simple or it could be just looking through the choices.


Mik wrote:

On Apr 28, 9:10 pm, Dave Peterson wrote:
First, I would guess that you really don't want this procedure to run for each
worksheet in the workbook. (I could be wrong, though!)

Second, it's a pretty good idea to specify the range that you want to monitor.
I used A2:J2 in my code, but it could be any range you want.

Third, these events have parms passed to them. Sh and Target in the version you
posted. Sh is the sheet that has the change. Target is the cell(s) (could be
more than one!) that changed.

(If you wanted this same routine for (say) 10 out of 15 worksheets in the
workbook, you could use Sh to find out if the sheet should be processed. Post
back with more details if that's the case.)

Fourth, since you did a nice job naming the types (Type 2, Type 3, ..., Type 8),
you can extract that last number and use it in the code to find the size of the
range.

So if A2 held type 5, then you'd drop down to A3 and look at 5 cells.
Target.offset(1,0) is dropping down one.
Target.offset(1,0).resize(5,1) makes it 5 rows by 1 column.

So if all that sounds reasonable...

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim myStr As String
Dim HowMany As String
Dim RngToCheck As Range

Set Target = Target.Cells(1) 'just a single cell!

'just check the changes in a specific range
'I used A2:J2, change this to match what you need
If Intersect(Target, Me.Range("A2:J2")) Is Nothing Then
Exit Sub
End If

myStr = UCase(Target.Value)

If myStr Like UCase("TYPE *") Then
'ok to continue
Else
'bad type # (this shouldn't happen, right???)
Exit Sub
End If

HowMany = Mid(myStr, 6) 'ignore the first 5 characters!

If IsNumeric(HowMany) = False Then
'not a valid option!
Exit Sub
End If

Set RngToCheck = Target.Offset(1, 0).Resize(CLng(HowMany), 1)

If Application.CountA(RngToCheck) 0 Then
MsgBox "Insufficient space!"
Else
MsgBox "Ok to continue" 'do you really want that?
End If

End Sub





Mik wrote:

On Apr 27, 11:01 pm, Dave Peterson wrote:
if isempty(activecell.offset(1,0).value) then
'single cell under the activecell

or maybe...

if application.counta(activecell.resize(2,1)) 0 then
'at least one of the cells (activecell or the one below it) has something
'in it

Mik wrote:

I wish to establish if an offset RANGE contains text.

so, activecell and the cell below can be merged, and then populated.

something like....

if Range(ActiveCell.Offset(0, 0), ActiveCell.Offset(1, 0)).value is
nothing then
my macro
else
my macro
end if

Can anybody please help?

--

Dave Peterson- Hide quoted text -

- Show quoted text -

Dave,

Thanks for your responce, however, I am having problems trying to get
it working.

What I am trying to achieve is...
Cells have pulldown menu's where the user can select 'Type 2' or 'Type
3' appointments and so on...
Type 2 will occupy two cells
Type 3 will occupy three cells etc..

If a space of only 2 cells are available say 'A3:A4', because A5 may
be occupied, then the user can not enter a Type 3 appointment, as
there is insufficient space (only 2 cells available, not 3).
So, your code .... If Application.CountA(ActiveCell.Resize(2, 1))
0 Then ...... should determine if sufficient space is available.
Hope this makes sense.

My code (shortened, as there are 7 Case scenarios) is as follows...

Positioned within 'This Workbook'

Private Sub Workbook_sheetChange(ByVal Sh As Object, ByVal Target As
Range)

Select Case ActiveCell
Case "Type 2"
If Application.CountA(ActiveCell.Resize(2, 1)) 0 Then
MsgBox ("Insufficient space")
Else
Range(ActiveCell.Offset(0, 0), ActiveCell.Offset(1,
0)).Select
MsgBox ("OK to continue")
End If

Case "Type 3"
If Application.CountA(ActiveCell.Resize(3, 1)) 0 Then
MsgBox ("Insufficient space")
Else
Range(ActiveCell.Offset(0, 0), ActiveCell.Offset(2,
0)).Select
MsgBox ("OK to continue")
End If

End Select
End Sub

Hope you can assist.
Thanks in advance.
Mik

--

Dave Peterson- Hide quoted text -

- Show quoted text -

Thanks for the reply.
I must inform you that I am a newbie, and I do not entirely understand
your code.

Answers to your initial questions...

1) I do not wish to run this code on all workshsheets. Just Sheet 1.
2) The range should be limited to B2:F36.
3) Understood.
4) The types could be kept with the name Type 1, Type 2 etc...,
however I may wish to change these names to actual appointment names,
rather than types.

I have added the code, and changed the range to B2:F6, but can't see
where i am going wrong.

Would it be possible to send you the Excel file? Or is this forbidden
on this site?
If I can send it, how is best ?

Mik

--

Dave Peterson