VBA column counts

[almonde]

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.

Please Login or Register  to view this content.

[Kaak]

Maybe youo can assign the adress to a string and filter the string:

Dim AddressStr as string

Address = ActiveCell.Address

etc. etc.

[Norman Jones]

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>