Incrementing Pointer One Cell Down (Row)

[SoCo]

Hi and thank you all for a great forum and resource. I find myself at age 60 excited by new things to learn in Excel. I am less than a novice with VB and am currently working on some code (developed with the assistance of one of our programmers). The code captures the output from a microbalance's serial port and writes the output to the currently selected cell (Excel 2007). In order to make the sheet efficient in the fashion it's most likely to be used I'd like to increment the pointer down one row to the next cell in preparation to accept the next entry. Mice are fine but why bother if you have to...

Here is the entire code as it currently exists but I'm assuming the additioanl code would need to be added following the activecell.activate line highlighted here in red.

Private Sub XMCommCRC1_OnComm()
    Static sInput As String
    Dim sTerminator As String
    Dim Buffer As Variant
    
    ' Branch according to the CommEvent property.
    Select Case XMCommCRC1.CommEvent
        Case XMCOMM_EV_RECEIVE
            Buffer = XMCommCRC1.InputData ' Use Input property for MSComm
            sInput = sInput & Buffer
            If Worksheets("Settings").Range("Terminator") = "CR/LF" Then
                sTerminator = vbCrLf
            Else
                sTerminator = vbCr
            End If
            If Right$(sInput, Len(sTerminator)) = sTerminator Then
                XMCommCRC1.PortOpen = False
                sInput = Left$(sInput, Len(sInput) - Len(sTerminator))
        Dim a As Integer
        Dim b As String

For a = 1 To Len(sInput)
    If (Mid(sInput, a, 1)) = "g" Then
        b = Mid(sInput, 1, a - 1)
        ActiveCell.Value = CDbl(b)
        ActiveCell.Activate        
Exit For
    End If
Next a
            sInput = ""
        End If
    End Select
    
End Sub

Any assistance or suggestions are greatly appreciated!

[HaHoBe]

Hi, SoCo,

instead of

        ActiveCell.Activate

which is not really doing much try

        ActiveCell.Offset(1, 0).Activate

which should go down one row in the same column.

Ciao,
Holger

[SoCo]

Ciao Signore Holger,

Ahhh Funziona!!
Molto bene! Grazie mille!

If one were to want to expore more about "ActiveCell" and its options where would you refer them?

[HaHoBe]

Hi, SoCo,

more information on topics like ActiveCell can be found in the VBE: press F2 to go to the Object Browser, type in ActiveCell and hit search. From there on you may find quite a lot of interesting aspects (the Application Model of Excel may be called rich on objects).

ActiveCell should only be used on the activesheet displayed - any other worksheet may have an ActiveCell when changed to but you may only directly advance the one (in the active window which equals the Excel application/instance).

I would take into consideration of using a slightly different approach as to search for the last used and visible row in a column and offset from there - data will always be lined up in that Column.

Instead of

        ActiveCell.Value = CDbl(b)
        ActiveCell.Offest(1, 0).Activate

I´d preferred something like

Cells(Cells(Rows.Count, "B").End(xlUp).Row + 1, "B").Value = CDbl(b)

which will look for the last entry of Column B in the active sheet and place the data into the next row (with Excel from 2007 there are a lot of entries to be made - 2^20-1 to be precise if a heading is in Row 1).

HTH
Holger

[SoCo]

A great suggestion, I'll insert that code and beta the new worksheet tool on a few our lab techs to see how it works out.

On another note Re:Object Compatibility, I tired to insert the XMCommCRC.xmcommcrc object (commandbutton1) into an Excel 2003 worksheet (VB 6.3) and the attempt "error'd out" on a "Cannot Insert Object"
dialog box...am I correct in assuming that perhaps the Specfic Control is backward incompatible with 2003? http://home.comcast.net/~hardandsoftware/xmcomm.htm

[HaHoBe]

Hi, SoCo,

I would like to ask you to open a new thread for the last question - I can´t be of much help with that topic (never have worked on that before - one of the many white spaces on my map of Excel Interaction). It´s more likely that you receive an answer to a new thread than to one which has a different title.

Ciao,
Holger

[SoCo]

Roger Wilco