CODEWITHSUNDEEP
vbaexcelrangemax
GT.
GT.

Reputation: 792

Find the max value in a number of cells and copy that value into another cell

I'm trying to find the max value in a number of cells and copy that value into another cell. I'd appreciate if you could help me because I have tried a lot of things and none of them have worked.

Here's my code:

Dim ROIrange As Range
Dim ROImax As Integer
For i = 1 To 30
      ROIrange = Range("E" & (i + 4), "H" & (i + 4), "K" & (i + 4), "N" & (i + 4), "Q" & (i + 4), "T" & (i + 4), "H" & (i + 4)).Select
      ROImax = Application.WorksheetFunction.Max(ROIrange)
      Range("B" & (i + 4)).Value = ROImax
      Next

I have two problems:

  1. Whenever I run it, the program says: enter image description here

and highlights the Range here:

ROIrange = Range("E"...

(TROUBLESHOOTING I DID THAT DIDN'T WORK - feel free to skip)

In my attempt to solve this, I changed the number of values I have in the range function from 7 to 2. This change allows the program to progress further but it ends up selecting a box which is not what I want because that involves the selection of other cells which I don't want evaluated.

However, even this program ran into a problem - I thought this was probably because the first row is completely empty (as will occur with the data set I'm working with sometimes and is unavoidable)

However, I changed it to start with i=2 which references a row that happens to have data. This simply resulted in this error:

enter image description here

Upvotes: 1

Views: 176

Answers (1)

RDJ
RDJ

Reputation: 191

Please try this:

Set ROIrange = Range("E" & (i + 4) & ",H" & (i + 4) & ",K" & (i + 4) & ",N" & (i + 4) & ",Q" & (i + 4) & ",T" & (i + 4) & ",W" & (i + 4))

Place the range of cells together as a single string.

Upvotes: 4

Related Questions