Establish if Offset RANGE contains text

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?
0
Mik
4/27/2010 9:39:18 PM
excel.programming 6508 articles. 2 followers. Follow

11 Replies
1054 Views

Similar Articles

[PageSpeed] 9

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
0
Dave
4/27/2010 10:01:56 PM
On Apr 27, 11:01=A0pm, Dave Peterson <peter...@verizonXSPAM.net> wrote:
> if isempty(activecell.offset(1,0).value) then
> =A0 'single cell under the activecell
>
> or maybe...
>
> if application.counta(activecell.resize(2,1)) > 0 then
> =A0 =A0'at least one of the cells (activecell or the one below it) has so=
mething
> =A0 =A0'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
0
Mik
4/28/2010 7:46:37 PM
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 <peter...@verizonXSPAM.net> 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
0
Dave
4/28/2010 8:10:54 PM
On Apr 28, 9:10=A0pm, Dave Peterson <peter...@verizonXSPAM.net> wrote:
> First, I would guess that you really don't want this procedure to run for=
 each
> worksheet in the workbook. =A0(I could be wrong, though!)
>
> Second, it's a pretty good idea to specify the range that you want to mon=
itor.
> I used A2:J2 in my code, but it could be any range you want.
>
> Third, these events have parms passed to them. =A0Sh and Target in the ve=
rsion you
> posted. =A0Sh is the sheet that has the change. =A0Target 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. =
=A0Post
> back with more details if that's the case.)
>
> Fourth, since you did a nice job naming the types (Type 2, Type 3, ..., T=
ype 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)
>
> =A0 =A0 Dim myStr As String
> =A0 =A0 Dim HowMany As String
> =A0 =A0 Dim RngToCheck As Range
>
> =A0 =A0 Set Target =3D Target.Cells(1) 'just a single cell!
>
> =A0 =A0 'just check the changes in a specific range
> =A0 =A0 'I used A2:J2, change this to match what you need
> =A0 =A0 If Intersect(Target, Me.Range("A2:J2")) Is Nothing Then
> =A0 =A0 =A0 =A0 Exit Sub
> =A0 =A0 End If
>
> =A0 =A0 myStr =3D UCase(Target.Value)
>
> =A0 =A0 If myStr Like UCase("TYPE *") Then
> =A0 =A0 =A0 =A0 'ok to continue
> =A0 =A0 Else
> =A0 =A0 =A0 =A0 'bad type # (this shouldn't happen, right???)
> =A0 =A0 =A0 =A0 Exit Sub
> =A0 =A0 End If
>
> =A0 =A0 HowMany =3D Mid(myStr, 6) 'ignore the first 5 characters!
>
> =A0 =A0 If IsNumeric(HowMany) =3D False Then
> =A0 =A0 =A0 =A0 'not a valid option!
> =A0 =A0 =A0 =A0 Exit Sub
> =A0 =A0 End If
>
> =A0 =A0 Set RngToCheck =3D Target.Offset(1, 0).Resize(CLng(HowMany), 1)
>
> =A0 =A0 If Application.CountA(RngToCheck) > 0 Then
> =A0 =A0 =A0 =A0 MsgBox "Insufficient space!"
> =A0 =A0 Else
> =A0 =A0 =A0 =A0 MsgBox "Ok to continue" 'do you really want that?
> =A0 =A0 End If
>
> End Sub
>
>
>
>
>
> Mik wrote:
>
> > On Apr 27, 11:01 pm, Dave Peterson <peter...@verizonXSPAM.net> wrote:
> > > if isempty(activecell.offset(1,0).value) then
> > > =A0 'single cell under the activecell
>
> > > or maybe...
>
> > > if application.counta(activecell.resize(2,1)) > 0 then
> > > =A0 =A0'at least one of the cells (activecell or the one below it) ha=
s something
> > > =A0 =A0'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 .... =A0 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"
> > =A0 =A0 =A0 If Application.CountA(ActiveCell.Resize(2, 1)) > 0 Then
> > =A0 =A0 =A0 =A0 =A0 =A0MsgBox ("Insufficient space")
> > =A0 =A0 =A0 Else
> > =A0 =A0 =A0 =A0 =A0 =A0Range(ActiveCell.Offset(0, 0), ActiveCell.Offset=
(1,
> > 0)).Select
> > =A0 =A0 =A0 =A0 =A0 =A0MsgBox ("OK to continue")
> > =A0 =A0 =A0 End If
>
> > Case "Type 3"
> > =A0 =A0 =A0 If Application.CountA(ActiveCell.Resize(3, 1)) > 0 Then
> > =A0 =A0 =A0 =A0 =A0 =A0MsgBox ("Insufficient space")
> > =A0 =A0 =A0 Else
> > =A0 =A0 =A0 =A0 =A0 =A0Range(ActiveCell.Offset(0, 0), ActiveCell.Offset=
(2,
> > 0)).Select
> > =A0 =A0 =A0 =A0 =A0 =A0MsgBox ("OK to continue")
> > =A0 =A0 =A0 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
0
Mik
4/28/2010 8:53:45 PM
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 <peter...@verizonXSPAM.net> 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 <peter...@verizonXSPAM.net> 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
0
Dave
4/28/2010 9:34:27 PM
On Apr 28, 10:34=A0pm, Dave Peterson <peter...@verizonXSPAM.net> wrote:
> 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 ta=
b and
> select view code). =A0Paste into that code window.
>
> You allow "Type #" in all 175 cells (all of B2:F36)???? =A0That seems kin=
d of
> weird to me.
>
> If you change the values, then the code won't work. =A0The modification c=
ould be
> simple or it could be just looking through the choices.
>
>
>
>
>
> Mik wrote:
>
> > On Apr 28, 9:10 pm, Dave Peterson <peter...@verizonXSPAM.net> wrote:
> > > First, I would guess that you really don't want this procedure to run=
 for each
> > > worksheet in the workbook. =A0(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. =A0Sh and Target in th=
e version you
> > > posted. =A0Sh is the sheet that has the change. =A0Target 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 process=
ed. =A0Post
> > > 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 s=
ize 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)
>
> > > =A0 =A0 Dim myStr As String
> > > =A0 =A0 Dim HowMany As String
> > > =A0 =A0 Dim RngToCheck As Range
>
> > > =A0 =A0 Set Target =3D Target.Cells(1) 'just a single cell!
>
> > > =A0 =A0 'just check the changes in a specific range
> > > =A0 =A0 'I used A2:J2, change this to match what you need
> > > =A0 =A0 If Intersect(Target, Me.Range("A2:J2")) Is Nothing Then
> > > =A0 =A0 =A0 =A0 Exit Sub
> > > =A0 =A0 End If
>
> > > =A0 =A0 myStr =3D UCase(Target.Value)
>
> > > =A0 =A0 If myStr Like UCase("TYPE *") Then
> > > =A0 =A0 =A0 =A0 'ok to continue
> > > =A0 =A0 Else
> > > =A0 =A0 =A0 =A0 'bad type # (this shouldn't happen, right???)
> > > =A0 =A0 =A0 =A0 Exit Sub
> > > =A0 =A0 End If
>
> > > =A0 =A0 HowMany =3D Mid(myStr, 6) 'ignore the first 5 characters!
>
> > > =A0 =A0 If IsNumeric(HowMany) =3D False Then
> > > =A0 =A0 =A0 =A0 'not a valid option!
> > > =A0 =A0 =A0 =A0 Exit Sub
> > > =A0 =A0 End If
>
> > > =A0 =A0 Set RngToCheck =3D Target.Offset(1, 0).Resize(CLng(HowMany), =
1)
>
> > > =A0 =A0 If Application.CountA(RngToCheck) > 0 Then
> > > =A0 =A0 =A0 =A0 MsgBox "Insufficient space!"
> > > =A0 =A0 Else
> > > =A0 =A0 =A0 =A0 MsgBox "Ok to continue" 'do you really want that?
> > > =A0 =A0 End If
>
> > > End Sub
>
> > > Mik wrote:
>
> > > > On Apr 27, 11:01 pm, Dave Peterson <peter...@verizonXSPAM.net> wrot=
e:
> > > > > if isempty(activecell.offset(1,0).value) then
> > > > > =A0 'single cell under the activecell
>
> > > > > or maybe...
>
> > > > > if application.counta(activecell.resize(2,1)) > 0 then
> > > > > =A0 =A0'at least one of the cells (activecell or the one below it=
) has something
> > > > > =A0 =A0'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 popul=
ated.
>
> > > > > > something like....
>
> > > > > > if Range(ActiveCell.Offset(0, 0), ActiveCell.Offset(1, 0)).valu=
e 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 g=
et
> > > > it working.
>
> > > > What I am trying to achieve is...
> > > > Cells have pulldown menu's where the user can select 'Type 2' or 'T=
ype
> > > > 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 ma=
y
> > > > 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 .... =A0 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 A=
s
> > > > Range)
>
> > > > Select Case ActiveCell
> > > > Case "Type 2"
> > > > =A0 =A0 =A0 If Application.CountA(ActiveCell.Resize(2, 1)) > 0 Then
> > > > =A0 =A0 =A0 =A0 =A0 =A0MsgBox ("Insufficient space")
> > > > =A0 =A0 =A0 Else
> > > > =A0 =A0 =A0 =A0 =A0 =A0Range(ActiveCell.Offset(0, 0), ActiveCell.Of=
fset(1,
> > > > 0)).Select
> > > > =A0 =A0 =A0 =A0 =A0 =A0MsgBox ("OK to continue")
> > > > =A0 =A0 =A0 End If
>
> > > > Case "Type 3"
> > > > =A0 =A0 =A0 If Application.CountA(ActiveCell.Resize(3, 1)) > 0 Then
> > > > =A0 =A0 =A0 =A0 =A0 =A0MsgBox ("Insufficient space")
> > > > =A0 =A0 =A0 Else
> > > > =A0 =A0 =A0 =A0 =A0 =A0Range(ActiveCell.Offset(0, 0), ActiveCell.Of=
fset(2,
> > > > 0)).Select
> > > > =A0 =A0 =A0 =A0 =A0 =A0MsgBox ("OK to continue")
> > > > =A0 =A0 =A0 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, =A0Type 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- Hide quoted text -
>
> - Show quoted text -

Dave,

No problem about the file.

I initially entered the code in This Workbook, now that i have entered
within the Sheet code i see that it works OK, with minor problem.

For example (below) 'Type 1' is entered in B1 and B4. The Type 1
occupies 1 cell only.
What i want to do in this example is enter 'Type 2' in cell B2. It
should fit as Type 2 would occupy 2 cells being B2:B3.
Unfortunately, the code you provided won't allow the entry of Type
2... it displays insufficient space.
Why is this? I can't see which bit of code needs changing.

          A             B            C
1                   Type 1
2                   Type 2
3
4                   Type 1
5
6

thanks
Mik
0
Mik
4/28/2010 9:56:29 PM
I screwed up.  I changed your question.  I was thinking that "Type 1" demanded a
single EXTRA cells under it.  "Type 7" wanted 7 extra cells.  (I figured your
original description couldn't possibly be correct <hehe>.)

You can just check the range you want (for example:  the "type 7" cell plus the
6 under it) to see if there's exactly one cell with a value (the "Type #"
entry).

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.Resize(CLng(HowMany), 1)
    
    If Application.CountA(RngToCheck) > 1 Then
        MsgBox "Insufficient space!"
    Else
        MsgBox "Ok to continue" 'do you really want that?
    End If

End Sub




Mik wrote:
> 
<<snipped>>
> 
> Dave,
> 
> No problem about the file.
> 
> I initially entered the code in This Workbook, now that i have entered
> within the Sheet code i see that it works OK, with minor problem.
> 
> For example (below) 'Type 1' is entered in B1 and B4. The Type 1
> occupies 1 cell only.
> What i want to do in this example is enter 'Type 2' in cell B2. It
> should fit as Type 2 would occupy 2 cells being B2:B3.
> Unfortunately, the code you provided won't allow the entry of Type
> 2... it displays insufficient space.
> Why is this? I can't see which bit of code needs changing.
> 
>           A             B            C
> 1                   Type 1
> 2                   Type 2
> 3
> 4                   Type 1
> 5
> 6
> 
> thanks
> Mik

-- 

Dave Peterson
0
Dave
4/29/2010 12:13:55 AM
On 29 Apr, 01:13, Dave Peterson <peter...@verizonXSPAM.net> wrote:
> I screwed up. =A0I changed your question. =A0I was thinking that "Type 1"=
 demanded a
> single EXTRA cells under it. =A0"Type 7" wanted 7 extra cells. =A0(I figu=
red your
> original description couldn't possibly be correct <hehe>.)
>
> You can just check the range you want (for example: =A0the "type 7" cell =
plus the
> 6 under it) to see if there's exactly one cell with a value (the "Type #"
> entry).
>
> Option Explicit
> Private Sub Worksheet_Change(ByVal Target As Range)
>
> =A0 =A0 Dim myStr As String
> =A0 =A0 Dim HowMany As String
> =A0 =A0 Dim RngToCheck As Range
>
> =A0 =A0 Set Target =3D Target.Cells(1) 'just a single cell!
>
> =A0 =A0 'just check the changes in a specific range
> =A0 =A0 'I used A2:J2, change this to match what you need
> =A0 =A0 If Intersect(Target, Me.Range("A2:J2")) Is Nothing Then
> =A0 =A0 =A0 =A0 Exit Sub
> =A0 =A0 End If
>
> =A0 =A0 myStr =3D UCase(Target.Value)
>
> =A0 =A0 If myStr Like UCase("TYPE *") Then
> =A0 =A0 =A0 =A0 'ok to continue
> =A0 =A0 Else
> =A0 =A0 =A0 =A0 'bad type # (this shouldn't happen, right???)
> =A0 =A0 =A0 =A0 Exit Sub
> =A0 =A0 End If
>
> =A0 =A0 HowMany =3D Mid(myStr, 6) 'ignore the first 5 characters!
>
> =A0 =A0 If IsNumeric(HowMany) =3D False Then
> =A0 =A0 =A0 =A0 'not a valid option!
> =A0 =A0 =A0 =A0 Exit Sub
> =A0 =A0 End If
>
> =A0 =A0 Set RngToCheck =3D Target.Resize(CLng(HowMany), 1)
>
> =A0 =A0 If Application.CountA(RngToCheck) > 1 Then
> =A0 =A0 =A0 =A0 MsgBox "Insufficient space!"
> =A0 =A0 Else
> =A0 =A0 =A0 =A0 MsgBox "Ok to continue" 'do you really want that?
> =A0 =A0 End If
>
> End Sub
>
>
>
>
>
>
>
> Mik wrote:
>
> <<snipped>>
>
> > Dave,
>
> > No problem about the file.
>
> > I initially entered the code in This Workbook, now that i have entered
> > within the Sheet code i see that it works OK, with minor problem.
>
> > For example (below) 'Type 1' is entered in B1 and B4. The Type 1
> > occupies 1 cell only.
> > What i want to do in this example is enter 'Type 2' in cell B2. It
> > should fit as Type 2 would occupy 2 cells being B2:B3.
> > Unfortunately, the code you provided won't allow the entry of Type
> > 2... it displays insufficient space.
> > Why is this? I can't see which bit of code needs changing.
>
> > =A0 =A0 =A0 =A0 =A0 A =A0 =A0 =A0 =A0 =A0 =A0 B =A0 =A0 =A0 =A0 =A0 =A0=
C
> > 1 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 Type 1
> > 2 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 Type 2
> > 3
> > 4 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 Type 1
> > 5
> > 6
>
> > thanks
> > Mik
>
> --
>
> Dave Peterson- Hide quoted text -
>
> - Show quoted text -

Dave,

This works great.
Thanks.

How would I perform the same task, if I were to re-label the Types?
So,
'Type 1' becomes 'Man'
'Type 2' becomes 'Exp'
'Type 3' becomes 'Fac'
etc...

Can you shed any light on this?

Thanks again.
Mik

0
Mik
4/29/2010 8:57:51 AM
First, you may have noticed that most responders are top posters in this forum. 
(Yep, it's different here!).

You may want to start doing the same.

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)

    'this probably would have been a good test in the other code, too.
    if mystr = "" then
        'the cell has been cleared
        exit sub 'or something else????
    end if
    
    Select Case myStr
        Case Is = UCase("man"): HowMany = 1
        Case Is = UCase("Exp"): HowMany = 2
        'and so on and so on
        Case Else
            HowMany = 0
    End Select
    
    If HowMany = 0 Then
        'something bad happened, right?
        Exit Sub 'or a warning or what??
    End If

    Set RngToCheck = Target.Resize(HowMany, 1)
    
    If Application.CountA(RngToCheck) > 1 Then
        MsgBox "Insufficient space!"
    Else
        MsgBox "Ok to continue" 'do you really want that?
    End If

End Sub




Mik wrote:
<<Snipped>> 
> Dave,
> 
> This works great.
> Thanks.
> 
> How would I perform the same task, if I were to re-label the Types?
> So,
> 'Type 1' becomes 'Man'
> 'Type 2' becomes 'Exp'
> 'Type 3' becomes 'Fac'
> etc...
> 
> Can you shed any light on this?
> 
> Thanks again.
> Mik

-- 

Dave Peterson
0
Dave
4/29/2010 12:23:43 PM
On 29 Apr, 13:23, Dave Peterson <peter...@verizonXSPAM.net> wrote:
> First, you may have noticed that most responders are top posters in this =
forum.
> (Yep, it's different here!).
>
> You may want to start doing the same.
>
> Option Explicit
> Private Sub Worksheet_Change(ByVal Target As Range)
>
> =A0 =A0 Dim myStr As String
> =A0 =A0 Dim HowMany As String
> =A0 =A0 Dim RngToCheck As Range
>
> =A0 =A0 Set Target =3D Target.Cells(1) 'just a single cell!
>
> =A0 =A0 'just check the changes in a specific range
> =A0 =A0 'I used A2:J2, change this to match what you need
> =A0 =A0 If Intersect(Target, Me.Range("A2:J2")) Is Nothing Then
> =A0 =A0 =A0 =A0 Exit Sub
> =A0 =A0 End If
>
> =A0 =A0 myStr =3D UCase(Target.Value)
>
> =A0 =A0 'this probably would have been a good test in the other code, too=
..
> =A0 =A0 if mystr =3D "" then
> =A0 =A0 =A0 =A0 'the cell has been cleared
> =A0 =A0 =A0 =A0 exit sub 'or something else????
> =A0 =A0 end if
>
> =A0 =A0 Select Case myStr
> =A0 =A0 =A0 =A0 Case Is =3D UCase("man"): HowMany =3D 1
> =A0 =A0 =A0 =A0 Case Is =3D UCase("Exp"): HowMany =3D 2
> =A0 =A0 =A0 =A0 'and so on and so on
> =A0 =A0 =A0 =A0 Case Else
> =A0 =A0 =A0 =A0 =A0 =A0 HowMany =3D 0
> =A0 =A0 End Select
>
> =A0 =A0 If HowMany =3D 0 Then
> =A0 =A0 =A0 =A0 'something bad happened, right?
> =A0 =A0 =A0 =A0 Exit Sub 'or a warning or what??
> =A0 =A0 End If
>
> =A0 =A0 Set RngToCheck =3D Target.Resize(HowMany, 1)
>
> =A0 =A0 If Application.CountA(RngToCheck) > 1 Then
> =A0 =A0 =A0 =A0 MsgBox "Insufficient space!"
> =A0 =A0 Else
> =A0 =A0 =A0 =A0 MsgBox "Ok to continue" 'do you really want that?
> =A0 =A0 End If
>
> End Sub
>
> Mik wrote:
>
> <<Snipped>>
>
>
>
>
>
> > Dave,
>
> > This works great.
> > Thanks.
>
> > How would I perform the same task, if I were to re-label the Types?
> > So,
> > 'Type 1' becomes 'Man'
> > 'Type 2' becomes 'Exp'
> > 'Type 3' becomes 'Fac'
> > etc...
>
> > Can you shed any light on this?
>
> > Thanks again.
> > Mik
>
> --
>
> Dave Peterson- Hide quoted text -
>
> - Show quoted text -

Dave,

Fantastic.
Does exactly what i wanted.

Appreciate your help.

Mik
0
Mik
4/29/2010 10:07:53 PM
But you do hate top posting!

Mik wrote:
> 
> On 29 Apr, 13:23, Dave Peterson <peter...@verizonXSPAM.net> wrote:
> > First, you may have noticed that most responders are top posters in this forum.
> > (Yep, it's different here!).
> >
> > You may want to start doing the same.
> >
> > 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)
> >
> >     'this probably would have been a good test in the other code, too.
> >     if mystr = "" then
> >         'the cell has been cleared
> >         exit sub 'or something else????
> >     end if
> >
> >     Select Case myStr
> >         Case Is = UCase("man"): HowMany = 1
> >         Case Is = UCase("Exp"): HowMany = 2
> >         'and so on and so on
> >         Case Else
> >             HowMany = 0
> >     End Select
> >
> >     If HowMany = 0 Then
> >         'something bad happened, right?
> >         Exit Sub 'or a warning or what??
> >     End If
> >
> >     Set RngToCheck = Target.Resize(HowMany, 1)
> >
> >     If Application.CountA(RngToCheck) > 1 Then
> >         MsgBox "Insufficient space!"
> >     Else
> >         MsgBox "Ok to continue" 'do you really want that?
> >     End If
> >
> > End Sub
> >
> > Mik wrote:
> >
> > <<Snipped>>
> >
> >
> >
> >
> >
> > > Dave,
> >
> > > This works great.
> > > Thanks.
> >
> > > How would I perform the same task, if I were to re-label the Types?
> > > So,
> > > 'Type 1' becomes 'Man'
> > > 'Type 2' becomes 'Exp'
> > > 'Type 3' becomes 'Fac'
> > > etc...
> >
> > > Can you shed any light on this?
> >
> > > Thanks again.
> > > Mik
> >
> > --
> >
> > Dave Peterson- Hide quoted text -
> >
> > - Show quoted text -
> 
> Dave,
> 
> Fantastic.
> Does exactly what i wanted.
> 
> Appreciate your help.
> 
> Mik

-- 

Dave Peterson
0
Dave
4/29/2010 10:17:36 PM
Reply:

Similar Artilces:

Excel Range from variable
Hi, I am writing a script that will copy certain cells from on sheet to another. The problem I am having is that the cells I need can appear in diferent places in the original sheet. I can copy a range like this: ws1.Range("C3:C" & row).Copy(ws2.Range("A2")) However if I try to change the row and column to a variable it does not work. I have tried: ws1.Range(.Cells(rRow & "," & rCol).Cells(row & "," & rCol)).Copy(ws2.Range("B2")) And this; ws1.Range(rRow & "," & rCol & "...

Specifying a Range.
Please bear with me. This is probably a painfully fundamental question. I need to specify a range of 48 cells in a sum formula. For example, =SUM(A10:A57). Is there another way to achieve the same results by subtraction? Something like, =SUM(The cell I want to end with - the quantity of cells)? Hopefully, the question makes sense. Any help would be greatly appreciated. Perhaps you mean something like this....... =G1-SUM(A10-A57) Vaya con Dios, Chuck, CABGx3 "Jocco" <Jocco@discussions.microsoft.com> wrote in message news:E07694E2-7F9C-4808-8E49-1319784B824E@microsoft....

Shading a range based on a single cell value.
How would I shade a certain range based on the value of a cell within that range. For example: When the value of A1 > 0, It would shade range (A1:A15) a specific color from the color pallet. On the Toolbar, try Format > Conditional Formatting You will need to use the 'Formula Is' option, Look in Help or go onto http://contextures.com/xlCondFormat02.html where you will find an excellent tutorial from Debra Dalgleish, Regards, Alan. "Drew" <Drew@discussions.microsoft.com> wrote in message news:D2709F3D-3C3D-40C5-9CE5-D2C8A087C998@microsoft.com... > How woul...

How to append text from beginning to a large file with the help of any MFC class like CFile or CStdioFile.. ??
Hi, How to append text from beginning to a large file with the help of any MFC class like CFile or CStdioFile.. ?? It is able to append the text to the end but how to do that from the starting.. That should not overwrite the content of the file.. How to do that ?? ===== Regards, Jigar Meht AFAIK, there is nothing which will do this automatically for you. If its a really large file, and you dont want to read it all at once, you may want to consider writing what you want to another temporary file and then append existing file to it and then delete/rename as needed. -------- Ajay Kalra ...

Intermittent
When using Office Outlook 2003 to access my MSN Hotmail Plus emails, I have an intermittent problem with access. I receive a popup that states: "Cannot establish a connection to server. Now working in offline." I have no problem connecting to the Internet with Internet Explorer or Mozilla Firefox. I am presently using Cox Cable Broadband to access the internet. Sometimes it takes two or three attempts to access my MSN Hotmail Plus email account using Office Outlook 2003. I am paying $19.95 a year for this email account. I would complain to MSN/Hotmail. "YNCMSS518...

importing text
Having a problem importing a text file. The file is rows of data with one tab delimiter within each row. Each row ends with a return. In each record is a row often containing a large amount of text. Sometimes not all of the text gets imported. It seems to get cut off at arbitrary locations, like the midde of a word. Like, here is a sample text: The Culver City Community Emergency Response Team (CERT) provides volunteer assistance to the Culver City community under the direction of the Culver City Fire Department in case of an earthquake, other disaster or major emergency. Training class...

Look up cell range?
Is there a way to use a search field to find a range of cells? With the range named from column D. If that makes sense. You could apply a data filter to show all the cells in column D that match your criteria. Hard to say more than that, given your question's brevity.... HTH, Bernie MS Excel MVP "doss04" <doss04@discussions.microsoft.com> wrote in message news:39BC9DA1-67CE-448D-82CE-B3A696A8C9E8@microsoft.com... > Is there a way to use a search field to find a range of cells? With the range > named from column D. If that makes sense. > > I ha...

Mail merge, text changes
I am trying to create certificates that I have made. I have four different fields that I merge into it. I have done this before with no problem. But now, when I select preview publication, everything looks good, when I am done I select "create a new publication" and when the new publication comes up the font in which the merged fields are in (not just the merged part of it, but all the text surrounding the merged parts) changes from Perpetua, which is what I want it to be, to Times New Roman. Does anyone have any ideas? I appreciate the help! ...

run-time error '9': subscript out of range
i get the message wheh i try to run this Private Sub Workbook_BeforeClose(Cancel As Boolean) Workbooks("DATA.XLS").Close SaveChanges:=False End Sub An error 9 indicates that you are attempting to access an item in a group when that item doesn't exist. Your code will throw an error 9 if there is no open workbook with the name "Data.xls". If you want to ignore the error, simply put "On Error Resume Next" on a line of code just above the code that closes the workbook. Note that using Resume to ignore an error in no way "fixes" the er...

change color of cell if an X exists in range
how do i change the color of a cell (red or black) depending on if an X is present in a range of cells please? a b c d 1 tim 2 pete x 3 jo x for example how to make tim black and pete in red because of c2 and jo in red because of d3 thanks K Hi Karen, Select Column A or Select all cells (Ctrl+A) depending on which cells you want to be colored. As long as Row 1 has the active cell then\ Format, Conditional Formatting C.F. Formula Is: =COUNTIF($B1:$G1,"x") then click o...

Shrinking font in Report Text Box
I have strings of varying length that I need to display in the same text box, so I would like to be able to shrink the text if it would otherwise be truncated. I've read other, similar posts but still have a question. It seems that Lebans AutoSizeFont is the right module for me to download (http://www.lebans.com/autosizefont.htm), but having done that, I'm not sure how to "apply" it to the text box in question. I've got no experience using modules. Could someone walk me through it? Thanks, Keith Keith wrote: >I have strings of varying length that I need to...

Clearing due to/from accounts- offset account?
I need to clear out due to/from accounts. What is best to be used as the offsetting account? Any help is appreciated!! Thanks in advance! Paul, some companies setup a 'Suspense' account with a P&L account number of '9999' or something similar for clearing balances if the clearing amounts net to zero. If they don't net to zero, you may want to run them through an 'Other Income/Expense' account. Frank Hamelly MCP-GP, MCT, MVP East Coast Dynamics www.eastcoast-dynamics.com ...

filtering multiple columns with ranges
using xls2002 - i have 4 columns of data- the first column has an account # - there may be several records with the same account # - I need to compare the number of records with the same account # and compare the data in the other 3 columns - for only those records wiht the same account # - I would like to write a formula that asks the queston - give me all Acct#s that have less than 4 ( or x number) different records, with all LPDate = 00/00/0000 & all for those records Dlisted date > 01/10/2002 & all balances less than 150 - I just can't get my hands around the formula to ch...

Establish if Offset RANGE contains text
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 esta...

Restrict a report to a given date range
I've been working on a report that summarizes a large amount of sales, returns, cost and variance data for a given month. I am happy with the results, but the next step is to take that to a YTD step. I think I can accomplish that fairly easily, but the one addition that needs to be added that I'm not sure how to accomplish is to be able to restrict it to certain date ranges in the year. All data has the month end date with it and the ranges will only need to be consecutive (Jan to March, not Jan and March but not Feb for example). What is the proper technique to accompl...

Re-establishing Links
When I open an certain Excel 2003 file with links, I am prompted to update the links that are in the workbook. Once "Update" is clicked, another dialog box with the option to "Continue" or "Edit Links?" opens. If "Continue" is selected, the file opens without updating, and if "Edit Links" is selected, I'm taken to where I have to re-establish links. The problem is that no files have been moved; the links should never have to be re-established. The files are located on two separate network drives, but they're drives that my co-...

How to imbed a cell reference in a text field?
Is there a way to imbed the value in a cell in a text field? Something like: "Cost at [E7] / hour.", where "[E7]" gets replaced by the value in cell E7. Thanks -- Running Excel 2000 SP-3 on Windows 2000 On Thu, 14 Dec 2006 05:48:30 -0800, LurfysMa <invalid@invalid.invalid> wrote: >Is there a way to imbed the value in a cell in a text field? > >Something like: "Cost at [E7] / hour.", where "[E7]" gets replaced by >the value in cell E7. I found the answer. Use the concatenate function: =CONCATENATE("Cost at $",E7,"...

Calender format changing when I change the date range
Hi all, I have a daily to list that I have used in pub 02 for years now I recently saved it in 03. I had 2 small calenders near the top that I customized changing the Month font, the date number colors, the bar at the top and had sat and sun boxes shaded. I have never had a problem before with this. Each time in the past I just selected the calender, changed the date range and everything else stayed the same, format wise. Now when I go to change the date range of the 2 calenders, say from October - November, the whole format of the calender changes back to the default. I thought this h...

Range as a Varable
I can take a named range such as DAT1 and change it to a variable then use it to say select i.e. Set ab = Range("DAT9") Set cd = Range("INT5") cd.Select but how do i use the varables in the next example Set isect = Application.Intersect(Range("DAT5"), Range("INT5")) I cant seem to figure this out thanks Rod Simply use: Set isect = Application.Intersect(ab, cd) HTH Otto "Rod Taylor" <trodney@bellsouth.net> wrote in message news:xbWUa.4064$jI6.1933@fe05.atl2.webusenet.com... > I can take a named range such a...

either draw bitmap of text
hi experts~ i'm developing a SDI application in winCE by using mfc. now i can draw either bitmap or text , but not both. that means when i draw bitmap , text can't show on the same time. but if i disable the bitmap drawing function, i can see the text. what's wrong with me? thank you. such as delete object. could anyone show me some example? thank you. "chichung" <kenneth@netxis.com.hk> �b�l�� news:ubYx8DiRDHA.2148@TK2MSFTNGP10.phx.gbl �����g... > hi experts~ > i'm developing a SDI application in winCE by using mfc. > > now i can draw either b...

How do you get more text to print in the lable of a monthly view?
Can text wrap? I want to get the entire title of my event in the window so that when I print it my boss can see the title and know what it is. How is that possible without changing the size of the text. There is nothing else on that date but it cuts off the text and one line. Can I change the size of the 'lable' box? "banannabee" <banannabee@discussions.microsoft.com> wrote in message news:538F8868-FB4F-4092-B95A-99E3C6DE48DE@microsoft.com... > Can text wrap? I want to get the entire title of my event in the window so > that when I print it my boss...

Chart with wide range of data
Hi I had a line chart displaying wide range of data Example: Training Expenses DeptA 100,000 DeptB 2,000 DeptC 150 DeptD 5,000 Tried to plot DeptA data in 2nd axis but feel that it is not easy to understand the chart cus there is a large gap between DeptA and DeptC data. Appreciate if anyone could advise or suggest better way of plotting this chart. TIA Conventional wisdom suggests using a log scale, but you're still have a wide span across all the data. Also, most people who read figures about training expenses for different departments are not well vers...

How to insert formula to a range of cells from VBA?
How do i insert the following formula from range e2 to j26? IF(OR(ISERROR(E2-TODAY()),ISBLANK(E2)),"Not Available",IF(E2-TODAY()<0,"Expendite",E2-TODAY()) ) This will create a circular reference. Record entry of the formula. This will give you the formula syntax in VBA. Then add it to this line of code... ThisWorkbook.Sheets(1).Range("E2:J26").FormulaR1C1 = '''Your recorded formula here Hth Oj Dont understand! "OJ" <oferns@gmail.com> wrote in message news:1110363041.616665.153130@f14g2000cwb.googlegroups.com... > This wil...

Returning a range of serial numbers when consecutive, otherwise a range of one.
/* The following problem is an attempt to create a stored procedure that will act as a datasource for a crystal report. The scenario presented is a simplified version of the main problem. Using the example below, we have two customers which received two different items. The items are serialized and are always returned from the customer. Items sent are tracked in one table. Items received are tracked in another table. We need a list of each customer, the item they were sent, and the serial numbers we have not yet received from them. Now finding a list of customers, items, and seri...

How do I advance the range by one row Excell VBA automatically
I am using Sheets(Array("Breakfast", "Lunch", "Supper", "Bedtime")).Select Range("B94:D98").Select but when I run this more than once it overwrites what was put in the first time. dim rng1 as range dim rng2 as range set rng1 = range("B94:D98") do while something 'or do until something set rng2 = rng1.offset(1,0)'rng2 is 1 row down from rng1 Sheets(Array ("Breakfast", "Lunch", "Supper", "Bedtime")).Select 'more code?? set rng1 = rng2 'move down 1 row. resets at loop start. lo...