Vba MsgBox - Question

Cuzzaa

Board Regular
Joined
Apr 30, 2019
Messages
86
Hi guys

I am using the below code to import a specific range of data from another spreadsheet into my current workbook I have open. The below code works perfectly.

However, what I am trying to achieve is to amend the code so that my current Msg box presents the following:

Instead of simply just saying 'BOM Import Successful' I would like it to say 'BOM Import Successful' and then on a new line I'd like to add some text, for e.g. 'x number' of items were not found. X number being the number of occurrences that contain the text 'Item Not Found' in data range J14:J64 (sheet is called 'Dashboard').

However, if the count finds 0 occuraces of 'Item Not Found' in data range J14:J64 then I would like the Msg box to simply say the following:

'BOM Import Successful' and then on a new line underneath 'All items were successfully imported!'

Is anyone able to help amend my code please?

Code:
Sub BOMTest()


' Get customer workbook...
Dim customerBook As Workbook
Dim filter As String
Dim caption As String
Dim customerFilename As String
Dim customerWorkbook As Workbook
Dim targetWorkbook As Workbook


' make weak assumption that active workbook is the target
Set targetWorkbook = Application.ActiveWorkbook


' get the customer workbook
filter = "Text files (*.xlsx),*.xlsx"
caption = "Please select the BOM "
customerFilename = Application.GetOpenFilename(filter, , caption)


Set customerWorkbook = Application.Workbooks.Open(customerFilename)


' assume range is A1 - C10 in sheet1
' copy data from customer to target workbook
Dim targetSheet As Worksheet
Set targetSheet = targetWorkbook.Worksheets(1)
Dim sourceSheet As Worksheet
Set sourceSheet = customerWorkbook.Worksheets(1)


targetSheet.Range("F14", "I48").Value = sourceSheet.Range("A2", "K48").Value


' Close customer workbook
customerWorkbook.Close


targetSheet.Range("F7").Value = customerFilename


MsgBox "BOM Import Successful!", vbInformation
End Sub
 
Last edited:
Change

Code:
aCount = WorksheetFunction.CountIf(Sheets("Dashboard").Range("J14:J64"), "Item Not Found")

to

Code:
aCount = WorksheetFunction.CountIf(Sheets("Dashboard").Range("J14:J64"), ">0")
 
Upvote 0

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
amend line that provides aCount to this
Code:
aCount = WorksheetFunction.CountIf(Sheets("Dashboard").Range("J14:J64"), ">0")

Thanks @Yongle

One more question...

If I were to use the below code, but I'd like to amend this so that it only counts the number of occurrences of 'Item Not Found' in cell range J14:J64 but ONLY if it matches a numerical value greater than zero in cell range I14:I64 (if it finds text or blanks in I14:I64 then it does not count any 'Item Not Found' occurrences in cell range J14:J64).

Similarly, if it finds zero occurrences of the above then it prints 'All items were successfully imported!'.

Are you able to help please? This is my last question, I promise! :D I'm extremely grateful for your help.
 
Upvote 0
Thanks @Yongle

One more question...

If I were to use the below code, but I'd like to amend this so that it only counts the number of occurrences of 'Item Not Found' in cell range J14:J64 but ONLY if it matches a numerical value greater than zero in cell range I14:I64 (if it finds text or blanks in I14:I64 then it does not count any 'Item Not Found' occurrences in cell range J14:J64).

Similarly, if it finds zero occurrences of the above then it prints 'All items were successfully imported!'.

Are you able to help please? This is my last question, I promise! :D I'm extremely grateful for your help.

Change

Code:
aCount = WorksheetFunction.CountIf(Sheets("Dashboard").Range("J14:J64"), "Item Not Found")

Code:
aCount = WorksheetFunction.COUNTIFS(Sheets("Dashboard").Range("J14:J64"),"Item Not Found",Sheets("Dashboard").Range("I14:I64"),">0")
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,953
Members
448,535
Latest member
alrossman

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