Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Populate Cell Date Value with Active X Calendar Control | Excel Worksheet Functions | |||
Difference between a Forms Control verus Active-X Control | Excel Discussion (Misc queries) | |||
how to set active x control | Excel Discussion (Misc queries) | |||
Adding a control button to insert a date in the active cell. | Excel Discussion (Misc queries) | |||
Tool Tip Text for Form control/ Active-X control | Excel Programming |