active cell control using scripts
 

Hello all,

I found a script for my excel form and basically what it does is
searches for the next available blank row and adds a record or whatever
I entered in the form.

As of now the script does a search on A1(col). Range(“A1”) and it
keeps going down until it finds a free cell in col A. so the first col
would be 1,0, sec col would be 1,1, third would be 1,2 and so forth.

What I need to do is do a search on col B and start the input from col
a (0,0) col b(1,1) col c(0,2) and soo forth.
Problem when I change the second line to B1 it doesn’t see col A as
(0,0) rather the (0,0) starts from col b.
I’m somewhat new to this but played with the cols but I can’t get it to
start from col A.
Any ideas?
copy/pasted part of the code for your view:

ActiveWorkbook.Sheets("doctors").Activate
Range("B1").Select
Do
If IsEmpty(ActiveCell) = False Then
ActiveCell.Offset(1, 0).Select
End If
Loop Until IsEmpty(ActiveCell) = True

ActiveCell.Value = info_external_id.Value

If option_physician = True Then
ActiveCell.Offset(0, 1).Value = "Y"
ElseIf option_referrals = True Then
ActiveCell.Offset(0, 14).Value = "Y"
End If

ActiveCell.Offset(0, 2) = info_external_id.Value
ActiveCell.Offset(0, 3) = info_last_name.Value


--
Robin01
------------------------------------------------------------------------
Robin01's Profile: http://www.excelforum.com/member.php...o&userid=35269
View this thread: http://www.excelforum.com/showthread...hreadid=562586

active cell control using scripts
 

Robin,

Your active cell is in column B. To access column A via the
"ActiveCell.Offset" property you need to use a -1 for the column reference.
(ie. ActiveCell.Offset(0,-1))

You could also use the cells property instead of offset. Cells(1,1) is
"A1", Cells(4,10) is "D10".

For a better understanding of the Offset property - in your VBA editor click
Help-Microsoft Visual Basic Help and type "Offset Property" - the first
entry should be the one you want to look at.

Mike

"Robin01" wrote:


Hello all,

I found a script for my excel form and basically what it does is
searches for the next available blank row and adds a record or whatever
I entered in the form.

As of now the script does a search on A1(col). Range(€œA1€�) and it
keeps going down until it finds a free cell in col A. so the first col
would be 1,0, sec col would be 1,1, third would be 1,2 and so forth.

What I need to do is do a search on col B and start the input from col
a (0,0) col b(1,1) col c(0,2) and soo forth.
Problem when I change the second line to B1 it doesnt see col A as
(0,0) rather the (0,0) starts from col b.
Im somewhat new to this but played with the cols but I cant get it to
start from col A.
Any ideas?
copy/pasted part of the code for your view:

ActiveWorkbook.Sheets("doctors").Activate
Range("B1").Select
Do
If IsEmpty(ActiveCell) = False Then
ActiveCell.Offset(1, 0).Select
End If
Loop Until IsEmpty(ActiveCell) = True

ActiveCell.Value = info_external_id.Value

If option_physician = True Then
ActiveCell.Offset(0, 1).Value = "Y"
ElseIf option_referrals = True Then
ActiveCell.Offset(0, 14).Value = "Y"
End If

ActiveCell.Offset(0, 2) = info_external_id.Value
ActiveCell.Offset(0, 3) = info_last_name.Value


--
Robin01
------------------------------------------------------------------------
Robin01's Profile: http://www.excelforum.com/member.php...o&userid=35269
View this thread: http://www.excelforum.com/showthread...hreadid=562586