Message Box value to percentage and currency

[gilquenanoviii]

Hi everyone. I am new here.
Please help me as I need to do and finish a report.

How will i be able to change the format of YTD Sales, MTD, Sales to $ currency format
and the TY vs LY LFL , Con. to Dept to Percentage Format?

Please help.

this is the formula that is in use for this message box


Sub ANKER()
Dim xRg As Range
Dim xCell As Range
Dim xStr As String
Dim xRow As Long
Dim xCol As Long
Worksheets("ELECTRONICS_DATA").Activate
Set xRg = Range("n16:m24")
For xRow = 1 To xRg.Rows.Count
For xCol = 1 To xRg.Columns.Count
xStr = xStr & xRg.Cells(xRow, xCol).Value & vbTab
Next
xStr = xStr & vbCrLf
Next
Worksheets("MAP").Activate
MsgBox xStr, vbInformation
End Sub


thank you so much in advance

 

[dmt32]

Hi,
welcome to forum

Not sure if this is what you are trying to do but assuming the ranges are all formatted in manner required then trying using the Text property of the Range to return what you see in the cell.

Sub ANKER()
    Dim xRg         As Range, xCell As Range
    Dim xRow        As Long, xCol As Long
    Dim xStr        As String
   
    Set xRg = Worksheets("ELECTRONICS_DATA").Range("n16:m24")
   
    For xRow = 1 To xRg.Rows.Count
        For xCol = 1 To xRg.Columns.Count
            xStr = xStr & xRg.Cells(xRow, xCol).Text & vbTab
        Next
        xStr = xStr & vbCrLf
    Next
   
    Worksheets("MAP").Activate
   
    MsgBox xStr, vbInformation
End Sub

Dave

 

[mohadin]

Hi
just another IDEA

'Option Explicit

Sub ANKER()
    Dim xRg As Range
    Dim xCell As Range
    Dim xStr As String
    Dim xRow As Long
    Dim xCol As Long
    Worksheets("ELECTRONICS_DATA").Activate
    Set xRg = Range("n16:m24")
    For xRow = 1 To xRg.Rows.Count
        For xCol = 1 To xRg.Columns.Count
        If xRg.Cells(xRow, xCol).Value = "YDT" Or xRg.Cells(xRow, xCol).Value = "MDT" Then
        xStr = xStr & xRg.Cells(xRow, 1).Value & vbTab & vbTab
        xStr = xStr & "$" & xRg.Cells(xRow, 2) & vbTab
        Exit For
        Else
         If xRg.Cells(xRow, xCol).Value = "TY vs LY LFL" Or xRg.Cells(xRow, xCol).Value = "Con" Then
        xStr = xStr & xRg.Cells(xRow, 1).Value & vbTab & vbTab
        xStr = xStr & "%" & xRg.Cells(xRow, 2) & vbTab
        Exit For
        Else
            xStr = xStr & xRg.Cells(xRow, xCol).Value & vbTab
            End If
            End If
        Next
        xStr = xStr & vbCrLf
    Next
    Worksheets("MAP").Activate
    MsgBox xStr, vbInformation
End Sub

 

[gilquenanoviii]

Hi
just another IDEA

'Option Explicit

Sub ANKER()
    Dim xRg As Range
    Dim xCell As Range
    Dim xStr As String
    Dim xRow As Long
    Dim xCol As Long
    Worksheets("ELECTRONICS_DATA").Activate
    Set xRg = Range("n16:m24")
    For xRow = 1 To xRg.Rows.Count
        For xCol = 1 To xRg.Columns.Count
        If xRg.Cells(xRow, xCol).Value = "YDT" Or xRg.Cells(xRow, xCol).Value = "MDT" Then
        xStr = xStr & xRg.Cells(xRow, 1).Value & vbTab & vbTab
        xStr = xStr & "$" & xRg.Cells(xRow, 2) & vbTab
        Exit For
        Else
         If xRg.Cells(xRow, xCol).Value = "TY vs LY LFL" Or xRg.Cells(xRow, xCol).Value = "Con" Then
        xStr = xStr & xRg.Cells(xRow, 1).Value & vbTab & vbTab
        xStr = xStr & "%" & xRg.Cells(xRow, 2) & vbTab
        Exit For
        Else
            xStr = xStr & xRg.Cells(xRow, xCol).Value & vbTab
            End If
            End If
        Next
        xStr = xStr & vbCrLf
    Next
    Worksheets("MAP").Activate
    MsgBox xStr, vbInformation
End Sub

OMG THANK YOU SO MUCH YOU SAVED MY LIFE!

 

[gilquenanoviii]

Hi,
welcome to forum

Not sure if this is what you are trying to do but assuming the ranges are all formatted in manner required then trying using the Text property of the Range to return what you see in the cell.

Sub ANKER()
    Dim xRg         As Range, xCell As Range
    Dim xRow        As Long, xCol As Long
    Dim xStr        As String
  
    Set xRg = Worksheets("ELECTRONICS_DATA").Range("n16:m24")
  
    For xRow = 1 To xRg.Rows.Count
        For xCol = 1 To xRg.Columns.Count
            xStr = xStr & xRg.Cells(xRow, xCol).Text & vbTab
        Next
        xStr = xStr & vbCrLf
    Next
  
    Worksheets("MAP").Activate
  
    MsgBox xStr, vbInformation
End Sub

Dave

THIS IS THE ONE I USED. THANK YOU SO MUCH GUYS! YOUVE BEEN REALLY HELPFUL!

 

[dmt32]

THANK YOU SO MUCH GUYS! YOUVE BEEN REALLY HELPFUL!

Glad we were able to help & appreciate the feedback

Dave