Please express Cells(j,1) mean

[sbv1986]

Hi all
I have code = sheets("sheet1").cells(j,1)
1. Now i written = sheets("sheet1").cells(j,"A")
2. Or = sheets("sheet1").cells(j,x) with x = sheets("MAIN").range("D1").value then I type A on D1 sheets("MAIN")

Please tell me 1. and 2. wrong code? If its can run so have any diffriend between Original and 1. and 2.

Thanks./.

 

[pike]

Hi sbv1986,
What is the value of j and are the sheet name spelt correctly
The example below prints to the immediate window

Sub test()
    Dim j As Long, X As String
    'Cells(Row,Column)
    j = 1
    X = Sheets("MAIN").Range("D1").Value
    Debug.Print Sheets("Sheet1").Cells(j, 1)
    Debug.Print Sheets("Sheet1").Cells(j, "A")
    Debug.Print Sheets("Sheet1").Cells(j, X)
    'With A on D1 sheets("MAIN")
End Sub

 

[sbv1986]

Hi sbv1986,
What is the value of j and are the sheet name spelt correctly
The example below prints to the immediate window

Sub test()
    Dim j As Long, X As String
    'Cells(Row,Column)
    j = 1
    X = Sheets("MAIN").Range("D1").Value
    Debug.Print Sheets("Sheet1").Cells(j, 1)
    Debug.Print Sheets("Sheet1").Cells(j, "A")
    Debug.Print Sheets("Sheet1").Cells(j, X)
    'With A on D1 sheets("MAIN")
End Sub

Thanks for your replay
j is row number with j = 2 to lastrow

So = Sheets("sheet1").Cells(j,1) the same as = Sheets("sheet1").Cells(j,X) with X on D1 sheets("MAIN")
and the same as Sheets("sheet1").Cells(j,"A") with A in sheets("sheet1")

 

[pike]

yep,
What problem are you having?

Sub test()
    Dim j As Long, X As String
    'Cells(Row,Column)
    j = 1
    X = Sheets("MAIN").Range("D1").Value
    Debug.Print Sheets("sheet1").Cells(j, 1).value
    Debug.Print Sheets("sheet1").Cells(j, "A").value
    Debug.Print Sheets("sheet1").Cells(j, X).value
    'With A on D1 sheets("MAIN")
End Sub

 

[sbv1986]

yep,
What problem are you having?

Sub test()
    Dim j As Long, X As String
    'Cells(Row,Column)
    j = 1
    X = Sheets("MAIN").Range("D1").Value
    Debug.Print Sheets("sheet1").Cells(j, 1).value
    Debug.Print Sheets("sheet1").Cells(j, "A").value
    Debug.Print Sheets("sheet1").Cells(j, X).value
    'With A on D1 sheets("MAIN")
End Sub

Thanks for your question.
I have below code to copy data from multiple workbook to exist wb with Range copy: A20:F73; Sheets copy: A000241
Now i want to Range and sheetname can changing. This mean I want Range copy = Sheet("Main").range("A1") and sheetname = Sheet("Main").range("A2"), F1, F2, F3, ...F6, Fn from = Sheet("Main").range("A3") to .range("An")

Would you see code below

Public Sub DATA_copy()Dim cn As Object, rs As Object, i As Byte, lr As Long, fso As Object
Set cn = CreateObject("adodb.connection")
Set fso = CreateObject("Scripting.FileSystemObject")
Sheets("huydong").Range("A1").CurrentRegion.Offset(1).ClearContents
    With Application.FileDialog(msoFileDialogOpen)
        .InitialFileName = ThisWorkbook.Path
        .Filters.Clear
        .Filters.Add "A00024", "*.xl*"
        .AllowMultiSelect = True
        .Show
        For i = 1 To .SelectedItems.Count
            cn.Open ("provider=Microsoft.ACE.OLEDB.12.0;data source=" & .SelectedItems(i) & ";mode=Read;extended properties=""Excel 12.0;hdr=no"";")
       
        Set rs = cn.Execute("select '" & fso.GetBaseName(.SelectedItems(i)) & "',f1,f2,f3,val(f4),val(f5),val(f6) from [A000241$A20:F73] ")
       
            lr = Sheets("huydong").Range("A" & Rows.Count).End(3).Row
            If Not rs.EOF Then Sheets("huydong").Range("A" & lr + 1).CopyFromRecordset rs
            rs.Close
            cn.Close
        Next
    End With
End Sub