Excel VBA, Get data from Msgbox into a worksheet cell

Question

I am reading a small amount of data from worksheet cells. I can see the data displayed in multiple Msgbox's. The data is there ok. When I try to get the Msgbox to enter this data into separate worksheet cells, it puts them all into one cell, overwriting each previous entry, OR using other code it just enters the last piece of data into multiple cells.

It should be something very simple to do?

Can you please show me how to get Msgbox data into worksheet cells?

Sub Macro3()
    Dim myLastRow As Variant
    Dim myRow As Integer
    Dim myFind As String
    Dim myMatch As String
    Dim myReplace As Variant
    Dim cell, myColumn As Range
    Dim arr() As Variant

    Sheets("Test").Select
    arr = Range("D2:D6")
    For Each myReplace In arr
    'MsgBox "" & myReplace

    Range("A2:A40").Select
    Set myColumn = Cells.Find(myReplace, After:=Range("A1"), LookAt:=xlWhole, SearchOrder:=xlByColumns)
    'MsgBox "" & myColumn
    'MsgBox "" & myColumn.Address
    myFind = myColumn.Offset(0, 1)
    MsgBox "" & myFind

     Range("E2").Value = myFind


    Next

End Sub

I'm not following. You're building the msgbox data yourself, what's the problem? `MsgBox` is a function that displays some message and returns a `vbMsgBoxResult` telling you what button was pressed to dismiss it - exactly what does *"get the Msgbox to enter this data into separate worksheet cells"* mean? — Mathieu Guindon, Sep 18 '17 at 13:36
Thanks for your answer. The Msgbox is just used so I can see the data is there. If you look at the last line, I did try to use Range command, but this only adds the data all into one cell, where the numbers overwrite eachother, or if I use "E2:E6", it puts only the last number (same number) into 5 cells. I am trying to get the numbers separately into different cells. Thanks — PGPK, Sep 18 '17 at 13:41
If I'm reading this correctly, `myFind` is a single cell's info. (The cell you found the `replace` in). You want that cell info in another single cell? `Range("A1").Value = myFind`, no? — BruceWayne, Sep 18 '17 at 13:43
Sorry for confusing everyone. I have 3 columns of data, columns 1 and 2 could be considered key-value pairs. I have an array which reads each cell in column 3, uses Find to find that number in column 1, gets the corresponding match in column 2, and then should write that number beside the original number in new column 4. But I do not get the sequential numbers, only repeats of the same number. — PGPK, Sep 18 '17 at 13:46
*"but this only adds the data all into one cell"* - well you're telling it to write in cell `E1`. Increment a counter at each iteration, and use that counter to determine where to write, instead of hard-coding a cell address. — Mathieu Guindon, Sep 18 '17 at 13:49
I'm a total beginner. Can you please show me how? Did I use the wrong For Each loop? THANKS — PGPK, Sep 18 '17 at 13:51

score 0 · Answer 1 · answered Sep 18 '17 at 13:37

In your code, when it says MsgBox..., you are telling Excel to show a message box with certain information (e.g. myFind). If you want the information in the sheet (or any sheet), you need to replace MsgBox... with a Range() clause. This is very basic VBA so any tutorial should get you there.

score -2 · Accepted Answer · answered Sep 18 '17 at 13:52

-2

Please try the below Code:

Sub Macro3()
    Dim myLastRow As Variant
    Dim myRow As Integer
    Dim myFind As String
    Dim myMatch As String
    Dim myReplace As Range
    Dim cell As Range
    Dim myColumn As Range
    Dim arr As Range
    Dim i As Integer

    i = 2

    Sheets("Test").Select
    Set arr = Range("D2:D6")
    For Each myReplace In arr
    'MsgBox "" & myReplace

    Range("A2:A40").Select
    Set myColumn = Cells.Find(myReplace.Value, After:=Range("A1"), LookAt:=xlWhole, SearchOrder:=xlByColumns)
    'MsgBox "" & myColumn
    'MsgBox "" & myColumn.Address
    myFind = myColumn.Offset(0, 1).Value


     If myFind <> "" Then

    MsgBox "" & myFind

     Cells(i, 5).Value = myFind

     i = i + 1
     End If

    Next
End Sub

answered Sep 18 '17 at 13:52

Nikolaos Polygenis

591
7
13

Sir! You are an absolute GENIUS! It worked perfectly. THANK YOU VERY MUCH! – PGPK Sep 18 '17 at 13:57
Accept the answer so that someone else with a similar question can see that the answer is okay – Nikolaos Polygenis Sep 18 '17 at 14:10
"Try this {code dump}" answers are terrible. OP copies the block, pastes it, and then moves on to ask yet another beginner question because they didn't learn anything. Give 'em a fishing rod, not a fish. – Mathieu Guindon Sep 18 '17 at 14:27
@Mat's Mug perhaps you have right, but you aren't right if you vote negative the answer because the answer works!! Anyone can learn if he wants....you can learn if you study a nice solution, especially if you are a beginner!!! Nobody knows everything... – Nikolaos Polygenis Sep 18 '17 at 14:55
This was my first time using this site. I was not aware how to vote or anything like that. When I read your post about voting, I clicked on the correct mark, and voted a few times in the POSITIVE. I have no idea why this site chose to give negative marks? I AM EXTREMELY GRATEFUL TO YOU. Thank You. – PGPK Sep 18 '17 at 15:11
In answer to Mat's Mug. I appreciate your help with this question. I am a total beginner. I was doing this little ditty of a vba program to help a friend process thousands of map references for a game map he is doing as a hobby. The last time I used vba was in 2011, when I also did something similar for myself. I am currently processing his excel files for him, for free of course! So on this occasion, yes I did just paste in the code I was given, but MY code was 99% correct, not bad for a total beginner eh? THIS SITE IS A GREAT RESOURCE, many thanks to you all. – PGPK Sep 18 '17 at 15:15
I have notified a moderator to see if he/she can change my positive votes, which for some reason are showing as negative votes. I wanted to vote positive of course. Thanks – PGPK Sep 18 '17 at 15:19
@PGPK votes are "frozen" after 5 minutes. You can revert your vote if/when Nikolaos edits their answer though - a moderator can't do that, your flag will likely be declined if it hasn't been already. Oh look! What an opportunity to add some actual meat and get *two* downvotes reversed! – Mathieu Guindon Sep 18 '17 at 15:28
FWIW I didn't solely downvote for being a code-dump answer. Using `.Select` is slow, inefficent, bug-prone, and should be avoided. `Cells` implicitly refers to the active sheet and the same identical code will behave differently if written in a worksheet's code-behind module; same for unqualified `Range` calls. Your code will blow up with error 91 if the `.Find` call finds nothing, the `Sheets` collection can contain `Chart` objects, and that code needs to be indented. *And* it's a code-dump with no explanation whatsoever. – Mathieu Guindon Sep 18 '17 at 15:49
@Mat'sMug you have right I didn't check the code for all situations, I just edit it to run for the specific conditions described by OP – Nikolaos Polygenis Sep 18 '17 at 19:37
I was able to process 57, 339 cells with data with no errors. THANK YOU! Nikolaos Polygenis, I am much obliged to you. Very sorry about the down votes. My error, sorry! – PGPK Sep 19 '17 at 07:37
have a nice day no problem!! – Nikolaos Polygenis Sep 19 '17 at 07:38

Excel VBA, Get data from Msgbox into a worksheet cell

2 Answers2