Correct my iRow value

[Big Lar]

I’m attempting to extract values from a UserForm TextBox and an open workbook with <?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>

[FONT=Verdana]ChDir "C:\MyCompany"<o:p></o:p>[/FONT]
[FONT=Verdana]Workbooks.Open Filename:="C:\MyCompany\QBI.xls"<o:p></o:p>[/FONT]
[FONT=Verdana]Set WS = Workbooks("QBI.xls").Worksheets("Sheet1")[/FONT]
[FONT=Verdana]iRow = WS.Cells(Rows.Count, 1) _<o:p></o:p>[/FONT]
[FONT=Verdana].End(xlUp).Offset(1, 0).Row[/FONT]
[FONT=Verdana]<o:p></o:p>[/FONT]
[FONT=Verdana]WS.Cells(iRow, 1).Value = Me.TextBox1.Value<o:p></o:p>[/FONT]
[FONT=Verdana]WS.Cells(iRow, 2).Value = "GrSales"<o:p></o:p>[/FONT]
[FONT=Verdana] WS.Cells(iRow, 3).Value = Me.TextBox33.Value<o:p></o:p>[/FONT]
[FONT=Verdana]‘Then…<o:p></o:p>[/FONT]
[FONT=Verdana]   WS.Cells(iRow, 4).Value = Workbooks("Data.xls").Worksheets("Sheet1").Cells(iRow, 34).Value<o:p></o:p>[/FONT]

<o></o>
<o></o>
Everything works except…<o></o>
The value returned to “QBI.xls” WS.Cells(iRow, 4) is from “Data.xls” Row5 instead of “Data.xls” last row.

I'm puzzled.<o></o>

 

[Comfy]

because iRow is the last row in QBI

try

ChDir "C:\MyCompany"
Workbooks.Open Filename:="C:\MyCompany\QBI.xls"
Set WS = Workbooks("QBI.xls").Worksheets("Sheet1")
iRow = WS.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row

WS.Cells(iRow, 1).Value = Me.TextBox1.Value
WS.Cells(iRow, 2).Value = "GrSales"
 WS.Cells(iRow, 3).Value = Me.TextBox33.Value
‘Then…
   WS.Cells(iRow, 4).Value = Workbooks("Data.xls").Worksheets("Sheet1").Cells(Workbooks("Data.xls").Worksheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row, 34).Value

 

[Jonmo1]

iRow is being defined by the last used row+1 in the book QBI Sheet1 Column A.

Try

WS.Cells(iRow, 4).Value = Workbooks("Data.xls").Worksheets("Sheet1").Cells(Rows.Count, 34).End(xlup).Value

 

[kpark91]

That is because iRow is set to be the last row of "QBI.xls", not "Data.xls"

You would need to redeclare the last row in Data.xls by using the same formula

 

[Big Lar]

Try

WS.Cells(iRow, 4).Value = Workbooks("Data.xls").Worksheets("Sheet1").Cells(Rows.Count, 34).End(xlup).Value

Well...I guess my brain is getting blurry from staring at the code.

Thanks jonmo1.