Need macro to find max/min in rows and then manipulate the data

Hi there,

I'm wondering if someone can help me do this in a macro:

1. for rows 2 to the '$' symbol that it must find by itself (located in column A)

2. search columns A to '&' symbol that it must find by itself (located in row 1)

3. for the rows it finds from 2 to the $ sign, it looks to find the max and min value of each row

4. it stores the max and min value

5. it subtracts the min value of each row from each value of the row

6. it divides each value of the row by the (max value of the row - min value of the row)





eg of table.
_______col1 col2 col3 col4 col5 col6
row 1: Date Time Cash Car Other &
row 2: 12 34 56 78 90
row 3: 11 23 45 76 92
row 4: $

the program would only run from row 2 to 3 (before it finds '$' in col 1)
the program would only run from col 1 to col 5 (before it finds '&' in row A)

it would find for row 2: min = 12, max = 90, then it would subtract each value in row 2 by 12 and then divide all the entries by (90-12=78)
to change row 2 to: 0 0.282 0.564 0.846 1

it would find for row 3: min = 11, max = 92, then it would subtract each value in row 2 by 11 and then divide all the entries by (92-11=81)
to change row 3 to: 0 0.148 0.420 0.802 1

Please could you attach a workbook?

The workbook is to be created by the user of the program

They will be the ones copying and pasting different tables on the spreadsheet for the program to work with

That is why the $ and & symbols are there. It is scale adjustment for the user depending on how many datasets they have.

Since I was unable to do that, i had put an example of what it was to be at the bottom half of my original post.

I have created an example

It is slightly different but the idea remains the same.

(see attached workbook)

Try this:

Sub x()

Dim nCol As Long, nRow As Long, rng As Range, rng2 As Range

nCol = Rows(1).Find(what:="&", after:=Range("A1"), Lookat:=xlWhole).Column - 1
nRow = Columns(1).Find(what:="$", after:=Range("A1"), Lookat:=xlWhole).Row - 1

Set rng = Range(Cells(2, 2), Cells(nRow, nCol))
For Each rng2 In rng.Rows
    Cells(rng2.Row, 11) = WorksheetFunction.Min(rng2)
    Cells(rng2.Row, 12) = WorksheetFunction.Max(rng2)
    Cells(rng2.Row, 13) = Cells(rng2.Row, 12) - Cells(rng2.Row, 11)
    Cells(rng2.Row, 11).Copy
    rng2.PasteSpecial Operation:=xlSubtract
Next rng2

End Sub

that doesn't encorporate the:

divide by (max - min) after the subtract does it?

In none of your posts have you once used the words "please" or "thank you" and after I provided you with that code all you can do is point out that I didn't do something which wasn't even in your attachment.

So I don't really see why I should bother to help you any more.