Button Macro to read values in a few ranges then concatenate & list values based on the results of the read

TnD_Guy

New Member
Joined
Sep 5, 2019
Messages
2
Okay, so I guess I cant attach a spreadsheet, but here's an Imgur link:

https://imgur.com/a/jiV0xGn

So what am I trying to do here? Well, let's say that I click the button that says "20k thru 30k". I want a macro that will read D5:D16, F5:F16, H5:H16, K5:K16, M5:M16, O5:O16, and then if the value of the cell is greater than 20000 but less than 30000, I want it to tell me some other info maybe in a pop up window or list it someplace in the sheet.

A result list might look like this:

GE-1-Step 1
GE-1-Step 2
GE-1-Step 3
RT-1-Step 1
RT-1-Step 2

...

... And so on. I have that sample spreadsheet but I can't upload it. Basically, it will list for me the corresponding info for each cell whose value falls within thethe range on the button label.

Thanks everybody!!! :)
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
Not sure what the policy is on bumping threads here, but I'm bumping this one. I really hope someone can give me some ideas here.
 
Upvote 0
Hi & welcome to MrExcel.
How about
Code:
Sub TnDGuy()
   Dim Ary As Variant
   Dim r As Long, c As Long
   Dim Msg As String
   
   Ary = Range("A2:O16").Value2
   For c = 4 To 8 Step 2
      For r = 4 To 15
         If Ary(r, c) > 20000 And Ary(r, c) < 30000 Then
            Msg = Msg & Join(Array(Ary(1, 3), Ary(r, 1), Ary(2, c - 1)), "-") & vbLf
         End If
      Next r
   Next c
   For c = 11 To 15 Step 2
      For r = 4 To 15
         If Ary(r, c) > 20000 And Ary(r, c) < 30000 Then
            Msg = Msg & Join(Array(Ary(1, 10), Ary(r, 1), Ary(2, c - 1)), "-") & vbLf
         End If
      Next r
   Next c
   MsgBox Msg
End Sub
 
Upvote 0
If you would prefer the output in a cell, you could use
Code:
Sub TnDGuy()
   Dim Ary As Variant
   Dim r As Long, c As Long
   Dim Msg As String
   
   Ary = Range("A2:O16").Value2
   For c = 4 To 8 Step 2
      For r = 4 To 15
         If Ary(r, c) > 20000 And Ary(r, c) < 30000 Then
            Msg = Msg & Join(Array(Ary(1, 3), Ary(r, 1), Ary(2, c - 1)), "-") & ", "
         End If
      Next r
   Next c
   If Msg <> "" Then Msg = Msg & vbLf
   For c = 11 To 15 Step 2
      For r = 4 To 15
         If Ary(r, c) > 20000 And Ary(r, c) < 30000 Then
            Msg = Msg & Join(Array(Ary(1, 10), Ary(r, 1), Ary(2, c - 1)), "-") & ", "
         End If
      Next r
   Next c
  Range("E19").Value = Msg
End Sub
 
Upvote 0
Cross posted https://www.excelforum.com/excel-pr...other-cells-based-on-results.html#post5189844

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules).
This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.
 
Upvote 0

Forum statistics

Threads
1,213,484
Messages
6,113,924
Members
448,533
Latest member
thietbibeboiwasaco

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
Back
Top