Solved: Why doesnt this work? I need someone smart!

[silentsound]

Hi
I have created the following which takes the values from one workbook and copies them into another workbook but with a space between each value. Yes, this is a slightly overcomplicated way of doing this! As shown by the msgbox the transmission variable doesnt pick up the values of the cells it is supposed to, but I can't understand why not. Any pointer as to why this is not working would be much appreciated. Please excuse this being stylistically poor!


Dim xlApp As New Excel.Application
Dim xlWB As Excel.Workbook
Set xlWB = Workbooks.Open("C:\Users\user\Desktop\received.xlsx", , False)
With xlApp
.Visible = True
End With
Dim bnumber As Integer
bnumber = InputBox("Please input the number to be received", "", 3)
Dim i As Integer
For i = xlFirstRow To xlFirstRow + bnumber - 1
Dim transmission As String
transmission = Workbooks("received").Worksheets("sheet1").Cells(i, 1).Text
MsgBox transmission
Workbooks("receipt code").Worksheets("sheet1").Cells(2 * i, 1) = transmission
Next i

[Bob Phillips]

It is difficult to see without the data, but what about this



Dim xlApp As New Excel.Application
Dim xlWB As Excel.Workbook
Set xlWB = Workbooks.Open("C:\Users\user\Desktop\received.xlsx", , False)
With xlApp
.Visible = True
End With
Dim bnumber As Long
Dim transmission As String
Dim i As Long
bnumber = InputBox("Please input the number to be received", "", 3)
For i = xlFirstRow To xlFirstRow + bnumber - 1
transmission = xlWB.Worksheets("sheet1").Cells(i, 1).Text
MsgBox transmission
Workbooks("receipt code.xlsx").Worksheets("sheet1").Cells(2 * i, 1) = transmission
Next i

[silentsound]

It is difficult to see without the data, but what about this



Dim xlApp As New Excel.Application
Dim xlWB As Excel.Workbook
Set xlWB = Workbooks.Open("C:\Users\user\Desktop\received.xlsx", , False)
With xlApp
.Visible = True
End With
Dim bnumber As Long
Dim transmission As String
Dim i As Long
bnumber = InputBox("Please input the number to be received", "", 3)
For i = xlFirstRow To xlFirstRow + bnumber - 1
transmission = xlWB.Worksheets("sheet1").Cells(i, 1).Text
MsgBox transmission
Workbooks("receipt code.xlsx").Worksheets("sheet1").Cells(2 * i, 1) = transmission
Next i


Hi
Firstly thank you for the help. Much appreciated honestly! That didn't seem to do the trick though, essentially the same thing happened. Something about the way the variable transmission is defined must be wrong but I can't see what. The data, by the way, are numbers in the format 000.000, and for the moment there are 3 of them, although the intention is to use 400 eventually.

kind regards
Silentsound

[lucas]

Is there no way you can post an example workbook?

[Aflatoon]

Could you clarify exactly what is happening, and what should be happening?

[mikerickson]

Perhaps
transmission = CStr(xlWB.Worksheets("sheet1").Cells(i, 1).Value)

Using the .Text property can sometimes have odd results.
e.g. if a cell is hidden, .Text returns vbNullString. If a cell is too narrow, .Text returns "####"

If a cell isn't visible, showing what is desired, its safer to use the .Value property.

[silentsound]

I am beginning to think I have made some fundamental error here...but anyhow... I have attached the workbook with the data in although it only consists of three numbers typed in cells A2:A4. They are visible, although I tried the Cstr suggestion which seemed to have no effect. Aflatoon, the problem is that the cells in the worksheet aren't being assigned to the variable transmission (see earlier pst for the vba)

Thank you very much for those suggestions

oh and as you can see its excel 2007 if that makes a difference

[Bob Phillips]

Dim xlApp As New Excel.Application
Dim xlWB As Excel.Workbook
Set xlWB = Workbooks.Open("C:\Users\user\Desktop\received.xlsx", , False)
With xlApp
.Visible = True
End With
Dim bnumber As Integer
bnumber = InputBox("Please input the number to be received", "", 3)
Dim i As Integer
For i = Range("A1").End(xlDown).Row To Range("A1").End(xlDown).Row + bnumber - 1
Dim transmission As String
transmission = Workbooks("received.xlsx").Worksheets("sheet1").Cells(i, 1).Text
MsgBox transmission
Workbooks("receipt code").Worksheets("sheet1").Cells(2 * i, 1) = transmission
Next i

[silentsound]

Thanks xld much appreciated! Thank you to everyone for your help.