VBA column reference

[Noz2k]

Ok, I'm building a little bit of code for users to fill in a little excel form, submit and then it automatically populates another sheet in the workbook.

Where I am falling down at the moment is in referencing the column. I can find the correct place. But the reference is as a number rather than a letter, so I either need to change this, or change the way that I reference the cell to post the value to.

Anyway here is the code at the moment

Private Sub Submit_Click()
 
Dim wsf         As Worksheet, _
    p           As String, _
    ste         As Range, _
    ste1        As Long, _
    rng         As Range, _
    rng1        As Long
 
Set wsf = Sheets("Fleet")
p = "Week" & wsf.Range("E5")
With Sheets(p).Range("A3:AE3")
Set ste = .Find(wsf.Range("E3"))
ste1 = ste.Column
End With
 
Y = 7
'Do Until Y = 29
On Error Resume Next
With Sheets(p).Range("A1:A200")
Set rng = .Find(wsf.Range("A" & Y))
rng1 = rng.Row
End With
 
MsgBox (ste1 & rng1)
MsgBox (wsf.Range("E" & Y))
'Sheets(p).Range(ste1 & rng1) = wsf.Range("E" & Y)
 
'Y = Y + 2
 
'Loop
 
End Sub

 

[Andrew Poulsom]

Use the cells property:

Sheets(p).Cells(rng1, ste1).Value = wsf.Range("E" & Y).Value

 

[Jonmo1]

Use Cells to refer to a column letter...

Cells(Row#, Column # OR Letter)

Cells(1,1)
Cells(1,"A")
Range("A1")
All the same thing.

If you need to refer to a multi cell range, use

Range(Cells(1,"A"), Cells(10,"A"))
Same as
Range("A1:A10")

If you need to qualify a sheet name using that syntax, you must specify it on the Range and both cells

With Sheets("Sheet1")
.Range(.Cells(1, "A"), .Cells(10,"A"))
End with


Hope that helps.

 

[schielrn]

Maybe try something like:

'Sheets(p).cells(rng1,ste1) = wsf.Range("E" & Y)

Hope that helps.

 

[Noz2k]

lol, thank you all for the prompt responses