VBA code to highlight unique data imported into a file?

[Mr2017]

Hi

I've got a macro which imports data with multiple Brands into a file.

Is there code that exists to list the names of populated cells in a Sheet?

So if the brand names Coke, Pepsi and Sprite existed in cells A1, A2, and A3, as below, is there a way that you can get the code to list the cell names in a message box? NB the code would ideally go to the end of the column, so if, on the next iteration, there was a fourth brand eg Dr Pepper, then the code would list all brands, including Dr Pepper.

And equally, if there were only two brands, then it would just list Coke and Pepsi, or whatever the two brands were in the list....

Coke

Pepsi

Sprite

The code for a message box is easy (MsgBox: "You've imported ..., ..., and .... into the file"). But I'm not sure how to do the first bit?


I guess I would need to use something like this, but I'm not sure how to modify it to read the names of cells A1, A2, A3, etc....

Your help would be appreciated.

   Sub Test2()
      ' Select cell A1, *first line of data*.
      Range("A1").Select
      ' Set Do loop to stop when an empty cell is reached.
      Do Until IsEmpty(ActiveCell)
         ' Insert your code here.
         ' Step down 1 row from present location.
         ActiveCell.Offset(1, 0).Select
      Loop
   End Sub

 

[JLGWhiz]

Your objective is not clear. Do you want the content of the cells listed on a separated sheet or somewhere on the current sheet? Or do you want to list the cell address for the imported items? And are the items in column A, beginning on row 2? Will the sheet refresh with each import or do they just add on to existing data?

 

[Mr2017]

Hi JLGWhiz

Thanks for your response.

The cells would be listed on a separate sheet.

The items in column A would begin in cell A1.

And the sheet would refresh with each import.

So to give you an example, let’s say I imported three files: two files had Coke products and one had Pepsi products.

I’ve got a macro which will remove the duplicates and paste the brands into Sheet 1, from cell A1.

After that, I’d like to run a loop which gives a message box that says “you’ve imported Coke and Pepsi files.”

And it would do this by checking the contents of the cells in column A of Sheet 1.

Does that make things any clearer?

Thanks in advance.

 

[JLGWhiz]

It still is not clear how you want to view the list since you state both a separate sheet and a message box, but here is some code ffor you play with. The code does both. it lists the data from column A of sheet one onto column A of Sheet 2, AND it displays a message box with the items.

Sub t()
Dim ar As Variant, txt As String
ar = Sheet1.Range("A1", Sheet1.Cells(Rows.Count, 1).End(xlUp)).Value
Sheet2.Range("A1").Resize(4) = ar 'list on separate sheet
    For i = 1 To Sheet1.Cells(Rows.Count, 1).End(xlUp).Row 'Use message box to list
        txt = txt & Sheet1.Cells(i, 1).Value & ", "
    Next
    txt = Left(txt, Len(txt) - 1)
    MsgBox "You have imported " & txt & "."
End Sub

 

[JLGWhiz]

This modifies the code to make the Resize statement dynamic.

Sub t()
Dim ar As Variant, txt As String
ar = Sheet1.Range("A1", Sheet1.Cells(Rows.Count, 1).End(xlUp)).Value
Sheet2.Range("A1").Resize(UBound(ar) + 1) = ar 'list on separate sheet
    For i = 1 To Sheet1.Cells(Rows.Count, 1).End(xlUp).Row 'Use message box to list
        txt = txt & Sheet1.Cells(i, 1).Value & ", "
    Next
    txt = Left(txt, Len(txt) - 1)
    MsgBox "You have imported " & txt & "."
End Sub

And what happened to the tool bar with all the stuff to help in doing these posts?

 

[Mr2017]

Hi JLGWhiz

Thanks for the posts.

I tried to run both Subs, however, but got a Run Time 424 error that said "Object required" and it stopped at these lines, in each Sub, respectively:

First Sub:

Sheet2.Range("A1").Resize(4) = ar 'list on separate sheet

Second Sub:

Sheet2.Range("A1").Resize(UBound(ar) + 1) = ar 'list on separate sheet

Do you know why the Subs would have stopped there?

Thanks in advance.

 

[JLGWhiz]

Do you have more than one sheet in your workbook? Also, this line

Sheet2.Range("A1").Resize(UBound(ar) + 1) = ar

needs to be changed to

Sheet2.Range("A1").Resize(UBound(ar)) = ar

I forgot that the array is base 1 instead of 0 in this case.

If you do not have a sheet in the workbook with code name Sheet2 it would throw that error. If you have more than one sheet then you cans substiture the name of a blank sheet for the Sheet2 code name. eg. Sheets(2). If you only have one sheet in the workbook then you would need to add a sheet for the codeto work with that line. If you only need the message box then delete that line.

 

[Mr2017]

Ok, thanks.

The second Sub worked, when I made the amendment you suggested above.

And, yes, there was a Sheet2 in the spreadsheet.

All good, now!

Thanks for your help!

 

[JLGWhiz]

You're welcome,
regards, JLG