Thread: Button Macro to read values in a few ranges then concatenate & list values based on the results of the read Thanks: 0 Likes: 0

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

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!!!   Reply With Quote

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

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.  Reply With Quote

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

Hi & welcome to MrExcel.
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  Reply With Quote

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

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  Reply With Quote

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

Cross posted https://www.excelforum.com/excel-pro...ml#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.  Reply With Quote

User Tag List

Tags for this Thread

button, ge-1-step, macros, read, vba  Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•