Next Cell Question

[wssparky]

How do I have VB6 find the next empty cell in a row. I know there are 7
cells so after cell 7 is full it drops down to the next row and starts
over ?

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

 

[Tom Ogilvy]

if isempty(xlApp.activecell.offset(0,1)) then
set cell = xlApp.ActiveCell.offset(0,1)
Else
set cell = xlApp.ActiveCell.end(xltoRight)(1,2)
End if
if cell.column > 7 then
set cell = cells(cell.row,1)
End if
cell.select

 

[wssparky]

You gave me just what I asked for, trouble is, I don't know
how to use it :/

This is my code. What I want to put in the cells is

ActiveSheet.Range("F11") = Form1.Label1.Caption

I don't know where to put it.
I'm getting an error : " Object Required "


_________________________________________________________
Sub EmpRecord()

Dim FileNamePath As String
Dim FileName As String
Dim S1 As Excel.Worksheet

Dim EmpNumber As String
Dim EmpName As String
Dim EName As String
Dim SheetName As String
Dim EmpRecord As String


EmpRecord = Form1.Readout
EmpName = ActiveSheet.Cells(2, 2)

ActiveSheet.Range("A10") = Format(Now, "Short Date")



If IsEmpty(xlApp.ActiveCell.Offset(0, 1)) Then <----- This is
where the error is
Set Cell = xlApp.ActiveCell.Offset(0, 1)
Else
Set Cell = xlApp.ActiveCell.End(xlToRight)(1, 2)

End If
If Cell.Column > 7 Then
Set Cell = Cells(Cell.Row, 1)
End If
Cell.Select


ActiveSheet.Range("F11") = Form1.Label1.Caption


Form1.Readout2 = Format("Employee Name : " + EmpName) & vbCrLf & _
Format("Employee Number : " + EmpRecord) & vbCrLf & _
Format("Time : " + Form1.Label1.Caption)
Form1.Readout = ""


Call Form1.CloseBook


End Sub


___________________________________________________

Thanks for your help. I do appreciate it.




*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

 

[Tom Ogilvy]

you said you were doing it from VB6, so xlapp is a reference to the excel
application. If you haven't established one, then it will cause an error
since it is an undefined variable - if you have, then replace xlapp with the
variable that holds that reference (you don't show any in your sample code).

Based on your code, there is no indication of where you are trying to find
the next blank cell. You give no starting point from which you are looking
for the next blank cell.

Sorry, but I can't determine what you need.

 

[wssparky]

Ok, that was that problem. Thanks, I didn’t see that.
It runs now, but it’s not putting the info in the cell.
The first cell if "F11". It is a VB6 program putting the results of a
question in an excel sheet. The Information going into the cell it the
"Form1.Label1.Caption".

Did I tell you I'm VERY new to VB ..... Self taught, can you tell? No
professional training what so ever.

Thanks for your help !!
___________________________________________________________

Sub EmpRecord()

Dim FileNamePath As String
Dim FileName As String
Dim S1 As Excel.Worksheet

Dim EmpNumber As String
Dim EmpName As String
Dim EName As String
Dim SheetName As String
Dim EmpRecord As String


EmpRecord = Form1.Readout
EmpName = ActiveSheet.Cells(2, 2)

ActiveSheet.Range("A10") = Format(Now, "Short Date")



If IsEmpty(m_XLApp.ActiveCell.Offset(0, 1)) Then
Set Cell = m_XLApp.ActiveCell.Offset(0, 1)
Else
Set Cell = m_XLApp.ActiveCell.End(xlToRight)(1, 2)

End If
If Cell.Column > 7 Then
Set Cell = Cells(Cell.Row, 1)
End If
Cell.Select

Form1.Label1.Caption

Form1.Readout2 = Format("Employee Name : " + EmpName) & vbCrLf & _
Format("Employee Number : " + EmpRecord) & vbCrLf & _
Format("Time : " + Form1.Label1.Caption)
Form1.Readout = ""


Call Form1.CloseBook


End Sub


___________________________________________________________

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

 

[Tom Ogilvy]

Find the next empty cell starting with F11, look only in columns A to G

Dim cell as Excel.Range, cell1 as Excel.Range
With xlapp.ActiveSheet
If isempty(.Range("F11")) or isempty(.Range("G11")) then
if isempty(.Range("F11")) then
set cell = .Range("F11")
else
set cell = .Range("G11")
end if
Else
for each cell1 in .Range("A12:G20")
if isempty(cell1) then
set cell = cell1
Exit For
end if
Next
End if
End With
cell.Value = Form1.Label1.Caption

 

[wssparky]

This worked out Great !!
Thank you Very Much !!

wssparky______________________

We learn by doing, and doing ……and doing ……


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!