What have I done wrong in VBA

[jameshunt83]

Can any please help me correct this so it works. The debug shows the error as being on this line. One day I may get the hang of this VBA!

Private Sub Add1_Click()
Dim Matref As Variant, wb As Workbook
Dim i As Long

i = 1
Matref = Range("I7").Text
Set wb = Workbooks.Open("t:\shared\stocklist.xlsx")

With Rows(Matref)
Do Until Columns(i).Text = ""
    i = i + 1
Loop
[COLOR="Red"]wb.Sheets("stock").Range(i, Matref).Value = Workbooks("stock.xlsm").Sheets("Stock-Control").Range("J7").Value[/COLOR]
End With

End Sub

 

[VoG]

Should it be?

wb.Sheets("stock").Cells(i, Matref).Value = Workbooks("stock.xlsm").Sheets("Stock-Control").Range("J7").Value

 

[jameshunt83]

No it still fails on that line. Error message is runtime error '1004': Application-defined or object-defined error

 

[VoG]

What is the code meant to do and what is in I7?

 

[jameshunt83]

In "I7" is just a number such as "2"

Basically what I want is the code to copy the value in "J7" in one workbook to the next available free cell on the row number in "I7" in another workbook

 

[VoG]

Try

Private Sub Add1_Click()
Dim Matref As Variant, wb As Workbook
Dim i As Long

Matref = Range("I7").Text
i = Cells(Matref, Columns.Count).End(xlToLeft).Column + 1

Set wb = Workbooks.Open("t:\shared\stocklist.xlsx")
wb.Sheets("stock").Cells(Matref, i).Value = Workbooks("stock.xlsm").Sheets("Stock-Control").Range("J7").Value
End With

End Sub