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


:confused: :confused:


--
almonde
------------------------------------------------------------------------
almonde's Profile: http://www.excelforum.com/member.php...o&userid=25257
View this thread: http://www.excelforum.com/showthread...hreadid=387480

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

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


:confused: :confused:


--
almonde
------------------------------------------------------------------------
almonde's Profile:
http://www.excelforum.com/member.php...o&userid=25257
View this thread: http://www.excelforum.com/showthread...hreadid=387480