Previous Topic Next Topic

Vba Help needed

Closed
Report
-
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
 -
Hello,

im having an issue, what im whant to achive is this. i want to be able to select ranges depending on the content of the cells in column A, and then copy the higest number in the same rows in column C. so lets say the data look like this

A B C

hello 3
hello 5
hello 6
hello 8
hey 2
hey 9
yo 10
yo 7
yo 15

what i then want, is to print the value 8 to a spesefic location and then print the number 9 to an other location, and the number 15 to an other location again. the data may not look like this and the length of the collumns is always different so the macro needs to be totaly dynamic.

hope it is possible to understand what i want to be able to achive.

im a total newbie to Vba, and i am grateful for any help.

1 reply

Answer 1 / 1
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
 804
It is possible to write a macro but it is becoming complicated.

I am giving a hybrid solution. see whether it is helpful to you

your data sheet will be like this

A B C D E F h1 h2 hello hey yo
hello 3 5 7 10
hello 5
hello 6
hello 8
hey 2
hey 9
yo 10
yo 7
yo 15

the entries in D1,E1,F1 are clear
if you have more unique entries in col. A you can extend
further than F1.

the formula in D2 is

=MATCH(MAX(IF($A$1:A$100=D1,B1:$B$100)),$B$1:$B$100,0)

NOW INVOKE THIS FORMULA BY CONTROL+SHIFT+ENTER
copy D2 to E2 abd F2(if necessary beyond)

note that the values in D2,E2,F2 give the ROW NUMBER of the maximum value in col B corresponding to different entries like hello etc in column A

now I have created a small macro 

Sub test()
Dim j As Integer, k As Integer, rng As Range
Worksheets("sheet2").Cells.Clear
Worksheets("sheet1").Activate
Set rng = Range(Range("d2"), Range("d2").End(xlToRight))
j = WorksheetFunction.Count(rng)
'MsgBox j
For k = 1 To j
Cells(Range("d2").Offset(0, k - 1).Value, "b").Copy Worksheets("sheet2").Cells(Rows.Count, "a").End(xlUp).Offset(1, 0)
Next k
End Sub



when you run the macro the number 5,7,10 are copied in sheet 2.
modify the macro to suit you
1
Ask a question