Hi
How can I define a dynamic range that will depend on the cell value of another sheet?
I want VBA to loop into the first sheet and identify the value of the first cell. Then go to the second sheet, identify all the cells that have the same value and define these cells as the range (cells with similar value are adjacent). Thanks
This is the section I need to change:
If z = Worksheets("Datab").Cells(j, 4) Then
RangeOfExchange = Worksheets("Datab").Cells(j, 4)
Here is the full script:
Sub Test3()
Dim i As Integer
Dim j as Integer
Dim z As String
Dim iLastRow As Integer 'the last row on Worksheets("Datab") column D
Dim iLastRowSummaryC As Integer
Dim sh1 As Worksheet
Dim sh2 As Worksheet 'the Worksheets("Datab")
Dim RangeOfExchange As Range
Set sh1 = ActiveWorkbook.Sheets("Summary")
Set sh2 = ActiveWorkbook.Sheets("Datab")
iLastRowSummaryC = sh1.[C9].End(xlDown).Row + 1
iLastRow = sh2.[D2].End(xlDown).Row + 1 'the last row in Worksheets("Datab")
i = 9
Do While i < iLastRowSummaryC 'we loop down to the last row + 1 populated in sheet Summary, Column C (populated by the previous macro)
z = Worksheets("Summary").Cells(i, 3)
j = 2
Do While j < iLastRow 'we loop down to the last row
If z = Worksheets("Datab").Cells(j, 4) Then
RangeOfExchange = Worksheets("Datab").Cells(j, 4)
End If
j = j + 1
Loop
i = i + 1
Loop
End Sub
Bookmarks