Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA column counts
Hi I'm new here, and very new to VBA. Im am writing a macro to automatically get information from gauges connected via rs232c with help from a excel add-in that provides worksheet functions to allow the gauges to be read. My problem is that i need to get the column letter from a column count, then write this letter to a cell. You can see below where i have commented what variable holds the column count and where i'd like it put. Any help would be much appreciated. Code: -------------------- Sub Oedometer() ' defines loads on each oedometer str_loada = InputBox(Prompt:="Enter Loading for Oedometer A(kPa)", Title:="Load Amount") str_loadb = InputBox(Prompt:="Enter Loading for Oedometer B(kPa)", Title:="Load Amount") ' sets up sheet for oedometer A Sheets("Sheet1").Select i_colcounta = ActiveSheet.UsedRange.Columns.Count i_cola = i_colcounta + 1 Cells(1, i_cola).Value = str_loada ' sets up sheet for oedometer B Sheets("Sheet2").Select i_colcountb = ActiveSheet.UsedRange.Columns.Count i_colb = i_colcountb + 1 Cells(1, i_colb).Value = str_loadb MsgBox "Click OK to begin data collection." ' collect data newHour = Hour(Now()) newMinute = Minute(Now()) newSecond = Second(Now()) + 10 ' time interval waitTime = TimeSerial(newHour, newMinute, newSecond) Application.Wait waitTime Sheets("Sheet3").Select Range("B3").Select ActiveCell.Value = i_cola ' place column letter here Range("A2:A6").Select Application.Run Macro:="Easy_Macro" End Sub -------------------- -- almonde ------------------------------------------------------------------------ almonde's Profile: http://www.excelforum.com/member.php...o&userid=25257 View this thread: http://www.excelforum.com/showthread...hreadid=387480 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA column counts
Maybe youo can assign the adress to a string and filter the string: Dim AddressStr as string Address = ActiveCell.Address etc. etc. -- Kaak ------------------------------------------------------------------------ Kaak's Profile: http://www.excelforum.com/member.php...fo&userid=7513 View this thread: http://www.excelforum.com/showthread...hreadid=387480 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA column counts
Hi Almonde,
Paste the following function into the code module: Function ColumnNumber(ColLetter) As Integer 'Chip Pearson ColumnNumber = Cells(1, ColLetter).Column End Function Then, in your sub, change: ActiveCell.Value = i_cola ' place column letter here to: ActiveCell.Value = ColumnLetter(i_cola) --- Regards, Norman "almonde" wrote in message ... Hi I'm new here, and very new to VBA. Im am writing a macro to automatically get information from gauges connected via rs232c with help from a excel add-in that provides worksheet functions to allow the gauges to be read. My problem is that i need to get the column letter from a column count, then write this letter to a cell. You can see below where i have commented what variable holds the column count and where i'd like it put. Any help would be much appreciated. Code: -------------------- Sub Oedometer() ' defines loads on each oedometer str_loada = InputBox(Prompt:="Enter Loading for Oedometer A(kPa)", Title:="Load Amount") str_loadb = InputBox(Prompt:="Enter Loading for Oedometer B(kPa)", Title:="Load Amount") ' sets up sheet for oedometer A Sheets("Sheet1").Select i_colcounta = ActiveSheet.UsedRange.Columns.Count i_cola = i_colcounta + 1 Cells(1, i_cola).Value = str_loada ' sets up sheet for oedometer B Sheets("Sheet2").Select i_colcountb = ActiveSheet.UsedRange.Columns.Count i_colb = i_colcountb + 1 Cells(1, i_colb).Value = str_loadb MsgBox "Click OK to begin data collection." ' collect data newHour = Hour(Now()) newMinute = Minute(Now()) newSecond = Second(Now()) + 10 ' time interval waitTime = TimeSerial(newHour, newMinute, newSecond) Application.Wait waitTime Sheets("Sheet3").Select Range("B3").Select ActiveCell.Value = i_cola ' place column letter here Range("A2:A6").Select Application.Run Macro:="Easy_Macro" End Sub -------------------- -- almonde ------------------------------------------------------------------------ almonde's Profile: http://www.excelforum.com/member.php...o&userid=25257 View this thread: http://www.excelforum.com/showthread...hreadid=387480 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
For FARAZ QURESHI - Column Entry Counts | Excel Discussion (Misc queries) | |||
average daily counts to weekly counts | Excel Discussion (Misc queries) | |||
Column Counts | Excel Discussion (Misc queries) | |||
Looking for a Function which counts the repeatition of text in a column | Excel Worksheet Functions | |||
Counts | Excel Programming |