active cell control using scripts

[Robin01]

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

[crazybass2]

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 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
>
>