If then (else?) ...

  • Thread starter Thread starter StargateFanFromWork
  • Start date Start date
S

StargateFanFromWork

After all these years I've been trying to get a handle on those IF THEN (and
ELSE) statements. For some reason, my brain has never grasped how do use
these. Intellectually I understand, and have always understood, I just
don't seem to get working code. There seems to be some sort of block for
getting effective, working code. I've fiddled yet again today for a long
time without getting this to work. I've looked at many examples in archives
but no luck.

Here is what my macro looks like now:
*********************************************************
Sub AddNewEntry()
'
ActiveSheet.Unprotect 'place at the beginning of the code

If Range("A2") <> "" Then
Application.Goto Reference:="R1C3"
Range("A2").Select
Else
Application.Goto Reference:="R1C3"
Range("A2").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Select
End If

ActiveSheet.Protect ' place at end of code
End Sub
*********************************************************
I'm trying to do this:
If (condition) Then
' do something
Else
' do something else
End If

but in testing the code, it isn't working properly. On both sheets this is
used on, the behaviour is exactly the same even though the conditions are
not the same and I should have different results.

So, if there is only a header in column A, only this is needed:
Application.Goto Reference:="R1C3"
Range("A2").Select

If there are more than 2 rows of data, then this will happen:
Application.Goto Reference:="R1C3"
Range("A2").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Select

Thanks in advance for any help. If I can just lick this, I know that I will
really have broken the barrier and will be able to do quite a bit more in
XL2K. :oD
 
I don't think the issue is with your understanding of IF THEN, I think it's
with your testing condition. If you are checking a cell to see if no data
is in it, try something like this

If IsEmpty([A2]) Then
[A2].Select
Else
[A2].End(xlDown).Offset(1, 0).Select
End If

This may not be what you need but I couldn't figure out why you used a goto
then a select right after it. So based on your explanation of what you are
trying to do, this checks cell A2 to see if its empty, if it is empty, then
select it, otherwise go down the column to the last data entry then select
the cell below it.
Paul D

: After all these years I've been trying to get a handle on those IF THEN
(and
: ELSE) statements. For some reason, my brain has never grasped how do use
: these. Intellectually I understand, and have always understood, I just
: don't seem to get working code. There seems to be some sort of block for
: getting effective, working code. I've fiddled yet again today for a long
: time without getting this to work. I've looked at many examples in
archives
: but no luck.
:
: Here is what my macro looks like now:
: *********************************************************
: Sub AddNewEntry()
: '
: ActiveSheet.Unprotect 'place at the beginning of the code
:
: If Range("A2") <> "" Then
: Application.Goto Reference:="R1C3"
: Range("A2").Select
: Else
: Application.Goto Reference:="R1C3"
: Range("A2").Select
: Selection.End(xlDown).Select
: ActiveCell.Offset(1, 0).Select
: End If
:
: ActiveSheet.Protect ' place at end of code
: End Sub
: *********************************************************
: I'm trying to do this:
: If (condition) Then
: ' do something
: Else
: ' do something else
: End If
:
: but in testing the code, it isn't working properly. On both sheets this
is
: used on, the behaviour is exactly the same even though the conditions are
: not the same and I should have different results.
:
: So, if there is only a header in column A, only this is needed:
: Application.Goto Reference:="R1C3"
: Range("A2").Select
:
: If there are more than 2 rows of data, then this will happen:
: Application.Goto Reference:="R1C3"
: Range("A2").Select
: Selection.End(xlDown).Select
: ActiveCell.Offset(1, 0).Select
:
: Thanks in advance for any help. If I can just lick this, I know that I
will
: really have broken the barrier and will be able to do quite a bit more in
: XL2K. :oD
:
:
 
Give this a go.

If IsEmpty(Range("A2")) Then
MsgBox "empty cell"
End If


--

Regards,

Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect

:
| After all these years I've been trying to get a handle on those IF THEN
(and
| ELSE) statements. For some reason, my brain has never grasped how do use
| these. Intellectually I understand, and have always understood, I just
| don't seem to get working code. There seems to be some sort of block for
| getting effective, working code. I've fiddled yet again today for a long
| time without getting this to work. I've looked at many examples in
archives
| but no luck.
|
| Here is what my macro looks like now:
| *********************************************************
| Sub AddNewEntry()
| '
| ActiveSheet.Unprotect 'place at the beginning of the code
|
| If Range("A2") <> "" Then
| Application.Goto Reference:="R1C3"
| Range("A2").Select
| Else
| Application.Goto Reference:="R1C3"
| Range("A2").Select
| Selection.End(xlDown).Select
| ActiveCell.Offset(1, 0).Select
| End If
|
| ActiveSheet.Protect ' place at end of code
| End Sub
| *********************************************************
| I'm trying to do this:
| If (condition) Then
| ' do something
| Else
| ' do something else
| End If
|
| but in testing the code, it isn't working properly. On both sheets this
is
| used on, the behaviour is exactly the same even though the conditions are
| not the same and I should have different results.
|
| So, if there is only a header in column A, only this is needed:
| Application.Goto Reference:="R1C3"
| Range("A2").Select
|
| If there are more than 2 rows of data, then this will happen:
| Application.Goto Reference:="R1C3"
| Range("A2").Select
| Selection.End(xlDown).Select
| ActiveCell.Offset(1, 0).Select
|
| Thanks in advance for any help. If I can just lick this, I know that I
will
| really have broken the barrier and will be able to do quite a bit more in
| XL2K. :oD
|
|
 
You are trying to make this harder than it needs to be. Post back with where
the cursor will wind up and why you need to select in the first place. Why
are you first going to c1 and then back to a2 before looking for the next
cell.

Maybe???
Sub addnewentryMAYBETHIS()
lr = Cells(Rows.Count, "a").End(xlUp).Row
If Cells(2, 1) = "" Then addif = 1
Cells(lr + addif, 1).Select
'NOT necessary to select to do something.
End Sub
 
Okay, I've not gotten anywhere in the sense that nothing I've tried works.
So thought I'd approach this from a completely different angle by explaining
instead what is needed:

If column A is empty but for the header, the cursor is to go to the top,
which is done by this action:
Application.Goto Reference:="R1C1"
Range("A2").Select

If, on the other hand, there are items in any row of column A, then this
happens (i.e., cursor goes to the first empty row in column A):
Application.Goto Reference:="R1C1"
Range("A2").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Select

This is probably much clearer.

Anywho, all I need is one example of working code and I'll probably be able
to modify it forever after. I'm just not getting it this first time around.

Thanks!

********************************************************************
 
You got me puzzled.

To me it seems that the suggestion of Paul:

If IsEmpty([A2]) Then
[A2].Select
Else
[A2].End(xlDown).Offset(1, 0).Select
End If

works perfectly. What do you want more?

If I combine all the suggestions and your own input I arrive at:

Application.Goto Reference:="R1C1"
Range("A2").Select
If Not IsEmpty([A2]) Then
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Select
End If

which does exactly the same as the code above, just a bit less
efficient.
Apart from wanting to help you I am really getting curious what it is
that you want and don't get.
 
Back
Top