CODEWITHSUNDEEP
vbaexcel
Oday Salim
Oday Salim

Reputation: 1147

Looping through ranges in different sheets (Excel)

I have two sheets: 'Project_Name' and 'Admin'.

I also have a set of data on both. I have a code that attempts to loop through data in both sheets, but it does not work.

Code works if loop is only done on one sheet, but not both. I am not sure what the problem is? 

Dim val1 As String
Dim val2 As String
Dim val3 As String

For i = 1 to LastRow

With Worksheets("Project_Name")
  .Range(i, 1).Value = val1
  .Range(i, 2).Value = val2
End With

With Worksheets("Admin")
  .Range(i, 1).Value = val3
End With

Next i

Upvotes: 2

Views: 3618

Answers (2)

JC Guidicelli
JC Guidicelli

Reputation: 1316

Try this and adapt the vars, it's work for me (note that .Range is remplaced by .Cells) :

Sub test2()

' Active workbook
Dim wb As Workbook
Set wb = ThisWorkbook
Dim i As Integer

'*******************************************
'Adapt this vars


'define your sheets
Dim ws_pname As Worksheet
Dim ws_admin As Worksheet
Set ws_pname = wb.Sheets("Project_Name")
Set ws_admin = wb.Sheets("Admin")

'define your values
Dim val1 As String
Dim val2 As String
Dim val3 As String

val1 = "test_val1"
val2 = "test_val2"
val3 = "test_val3"

'definie the last Rows
Dim lastRow_pname As Integer
Dim lastRow_admin As Integer

lastRow_pname = ws_pname.Range("A" & Rows.Count).End(xlUp).Row + 1
lastRow_admin = ws_admin.Range("A" & Rows.Count).End(xlUp).Row + 1
'*******************************************

For i = 1 To lastRow_pname


With ws_pname
  .Cells(i, 1).Value = val1
  .Cells(i, 2).Value = val2
End With

Next i

For i = 1 To lastRow_admin
With ws_admin
  .Cells(i, 1).Value = val3
End With
  Next i

End Sub

EDIT : Code below to have only one loop.

Sub test2()

' Active workbook
Dim wb As Workbook
Set wb = ThisWorkbook
Dim i As Integer

'*******************************************
'Adapt this vars


'define your sheets
Dim ws_pname As Worksheet
Dim ws_admin As Worksheet
Set ws_pname = wb.Sheets("Project_Name")
Set ws_admin = wb.Sheets("Admin")

'define your values
Dim val1 As String
Dim val2 As String
Dim val3 As String

val1 = "test_val1"
val2 = "test_val2"
val3 = "test_val3"

'definie the last Rows
Dim lastRow_pname As Long
Dim lastRow_admin As Long
Dim lastRow As Long

lastRow_pname = ws_pname.Range("A" & Rows.Count).End(xlUp).Row + 1
lastRow_admin = ws_admin.Range("A" & Rows.Count).End(xlUp).Row + 1
'*******************************************

'determine biggest last row
If lastRow_pname >= lastRow_admin Then
    lastRow = lastRow_pname
Else
    lastRow = lastRow_admin
End If


For i = 1 To lastRow

    If i <= lastRow_pname Then
      With ws_pname
        .Cells(i, 1).Value = val1
        .Cells(i, 2).Value = val2
      End With
    End If

    If i <= lastRow_admin Then
      With ws_admin
        .Cells(i, 1).Value = val3
      End With
    End If

Next i

End Sub

Upvotes: 2

J.Doe
J.Doe

Reputation: 596

.Range(i, 2) seems wrong. Prefer Cells(Row,Column) when looping through rows/columns.

For i = 1 to LastRow

With Worksheets("Project_Name")
  .Cells(i, 1).Value = val1
  .Cells(i, 2).Value = val2
End With

With Worksheets("Admin")
  .Cells(i, 1).Value = val3
End With

Next i

Is working fine on both sheets on my set up.

Upvotes: 1

Related Questions