Forum Discussion
How to extracting a data between column range. Column range will vary.
I wanted to extract data that fall into my category using if function. The data shall be extracted within multiple ranges and the range will vary. Need a solution for this. Thank you.
OliverScheurich thanks for the response.
I wanted to do sample as shown below. Thanks for the help.
10 Sweet
11 Sour
25 Sour
15 Sweet
12 Sour
16 Sour
23 Sour
16 Sweet
Data required
1. Max value of range inside the range of sweet
Range 1 = 25
Range 2 = 23
Thanks for the help
Sub sweet()
Dim i As Integer
Dim j As Integer
Dim row1 As Integer
Dim max As Integer
Range("E:E").Clear
row1 = Application.WorksheetFunction.Match("sweet", Range(Cells(1, 2), Cells(8, 2)), 0)
j = 1
For i = 1 To 15
If Cells(row1 + i, 2).Value = "sweet" Then
max = Application.WorksheetFunction.max(Range(Cells(row1 + 1, 1), Cells(row1 + i - 1, 1)))
row1 = row1 + i
Cells(j, 5).Value = max
j = j + 1
i = 0
Else
End If
Next i
End SubMaybe with these lines of code. I assume that there is at least one time sour within two times sweet and not more than 14 times sour within two times sweet. You can click the button in cell G2 in the attached file to start the macro.