multiple Minimums in VBA

iknowu99

Well-known Member
Joined
Dec 26, 2004
Messages
1,158
Aloha

Column A contains integers; I would like to identify the minimum/s. The identifying process adds 1 to column Z and in the same row as the minimum/s.

so far I have:
Set myRange = Worksheets("temp").Range("A1:A" & Range("A65536").End(xlUp).Row)
answer = Application.WorksheetFunction.Min(myRange)




Gracias
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
Hi,

with some sample data you are more likely to get a suitable answer
is this of any help ?
   A    B  
 1 data    
 2 5    0  
 3 2    0  
 4 3    0  
 5 2    0  
 6 1    1  
 7 2    0  
 8 2    0  
 9 1    1  
10 5    0  

Picking list

[Table-It] version 06 by Erik Van Geit
Code:
RANGE   FORMULA (1st cell)
B2:B10  =--(A2=MIN($A$2:$A$10))

[Table-It] version 06 by Erik Van Geit

kind regards,
Erik
 

iknowu99

Well-known Member
Joined
Dec 26, 2004
Messages
1,158
This is the result I'm going for except in VBA format. How does one program to do like this to Column B?
 

iknowu99

Well-known Member
Joined
Dec 26, 2004
Messages
1,158
the VBA code would need to add 1 to column B, usually column B will already have values


thanks
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832

ADVERTISEMENT

the VBA code would need to add 1 to column B, usually column B will already have values
why did you mention column B will have values already ? is this important in anyway ?

try this
Code:
Sub test()
Dim LR as Long
Dim rng As Range

LR = Cells(Rows.Count, "A").End(xlUp).Row
Set rng = Range("A2:A" & LR)

    With Rng.Offset(0, 1)
    .Formula = "=--(A2=MIN($A$2:$A$" & LR & "))"
    .Value = .Value
    End With

End Sub
written off-hand: typos and errors possible & not tested: but I think it will be very close

best regards,
Erik
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
perhaps I understand now, just after posting :)

you mean you want to add 1 in column B to all rows which have a minimum

then try this
Code:
Option Explicit

Sub test()
Dim LR As Long
Dim rng As Range

LR = Cells(Rows.Count, "A").End(xlUp).Row
Set rng = Range("A2:A" & LR)

Application.ScreenUpdating = False
Columns(2).Insert

    With rng.Offset(0, 1)
    .Formula = "=--(A2=MIN($A$2:$A$" & LR & "))+C2"
    .Offset(0, 1).Value = .Value
    End With
    
Columns(2).Delete
Application.ScreenUpdating = True
End Sub
 

iknowu99

Well-known Member
Joined
Dec 26, 2004
Messages
1,158

ADVERTISEMENT

thankyou erik but there seems to be a problem: my main goal is to select what min values are in Column A in worksheet "temp" and add one to the same row Column B in worksheet "main"

in your code what does +C2 do? there's also no need to create a new column since worksheet temp's sole purpose is to identify the min. Only Column A is used in "temp" and after mins are found the temp worksheet is cleared
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
two reasons for some "grrr" :x in my mind
1. you were not clear in stating the problem, hence we turn in circles: so think about a bit and repost with your final version of the question
you have read my quote
with some sample data you are more likely to get a suitable answer
and you didn't tell my sample data were not OK :confused:

2. you ask questions and give comments about the code, but it doesn't seem to me you tested it: my first one is doing what you have seen in the example I posted and the second code is adding up to the existing values in column B
there won't be an inserted column at the end of the code, it's just a trick to calculate (another method would be to use an array "in memory")

in the mean time you can try editing the code to get results on your other sheet:
instead of "+C2", you can put something like
+sheetname!B2

waiting for clear problemdescription if still needed
Erik
 

Forum statistics

Threads
1,141,626
Messages
5,707,483
Members
421,510
Latest member
haroonstr

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top