Formatting in excel

S

smithy0705

New Member
Joined
Feb 20, 2019
Messages
8
Hi

I am very new to VBA basically copying bits from other workbooks so please bear with me!

My query is as follows:

I would like to reformat my output of

[TABLE="width: 201"]
<colgroup><col width="268" style="width: 201pt; mso-width-source: userset; mso-width-alt: 11434;"> <tbody>[TR]
[TD="width: 268, bgcolor: yellow"]12.9000299280694 - 63.5578583765112[/TD]
[/TR]
</tbody>[/TABLE]

to £12.90 - £63.58

Please can anyone help.
Thanks so much in advance
Joanne
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
Sort by date Sort by votes
What is the current code that produces that output?
 
Upvote 0
Sheets("Output").Select
Range("A5").Select

If MAXVALUE = MINVALUE Then
RENTVALUE = MINVALUE
Else
RENTVALUE = MINVALUE & " - " & MAXVALUE
End If

ActiveCell.Offset(I, 3) = RENTVALUE
 
Upvote 0
You can use Format

Code: 
If MAXVALUE = MINVALUE Then
RENTVALUE = Format$(MINVALUE, "£#,##0.00")
Else
RENTVALUE = Format$(MINVALUE, "£#,##0.00") & " - " & Format$(MAXVALUE, "£#,##0.00")
End If
 
Upvote 0
Thank you so much for this amazing!

Can you also help to remove/ignore any zeros?

This is my raw data:
[TABLE="width: 100"]
<colgroup><col width="133" style="width: 100pt; mso-width-source: userset; mso-width-alt: 5674;"> <tbody>[TR]
[TD="width: 133, bgcolor: #FFC000"]38.07[/TD]
[/TR]
[TR]
[TD="bgcolor: #FFC000"]160.00[/TD]
[/TR]
[TR]
[TD="bgcolor: #FFC000"]62.33[/TD]
[/TR]
[TR]
[TD="bgcolor: #FFC000"]50.00[/TD]
[/TR]
[TR]
[TD="bgcolor: #FFC000"]50.01[/TD]
[/TR]
[TR]
[TD="bgcolor: #FFC000"]0.00[/TD]
[/TR]
[TR]
[TD="bgcolor: #FFC000"]0.00
[/TD]
[/TR]
[TR]
[TD="bgcolor: #FFC000"]0.00[/TD]
[/TR]
</tbody>[/TABLE]


Thank you
 
Upvote 0
What do you expect the output to be if one of the values is 0 and the other isn't?
 
Upvote 0
Thank you for responding and helping me I'm very grateful!

[TABLE="width: 588"]
<colgroup><col width="61" style="width: 46pt; mso-width-source: userset; mso-width-alt: 2230;"> <col width="203" style="width: 152pt; mso-width-source: userset; mso-width-alt: 7424;"> <col width="161" style="width: 121pt; mso-width-source: userset; mso-width-alt: 5888;"> <col width="136" style="width: 102pt; mso-width-source: userset; mso-width-alt: 4973;"> <col width="222" style="width: 167pt; mso-width-source: userset; mso-width-alt: 8118;"> [TR]
[TD="width: 264, bgcolor: transparent, colspan: 2"]This is my raw data[/TD]
[TD="width: 161, bgcolor: transparent"][/TD]
[TD="width: 136, bgcolor: transparent"][/TD]
[TD="width: 222, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: yellow"]External ID[/TD]
[TD="bgcolor: yellow"]Name[/TD]
[TD="bgcolor: yellow"]Amounts£[/TD]
[TD="bgcolor: yellow"]Date1[/TD]
[TD="bgcolor: yellow"]Date2[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]CODE1[/TD]
[TD="bgcolor: transparent"]ENTITY ONE[/TD]
[TD="bgcolor: transparent"]26.96[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]06/09/2022[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]CODE1[/TD]
[TD="bgcolor: transparent"]ENTITY ONE[/TD]
[TD="bgcolor: transparent"]18.47[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]23/06/2019[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]CODE1[/TD]
[TD="bgcolor: transparent"]ENTITY ONE[/TD]
[TD="bgcolor: transparent"]63.56[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]22/07/2024[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]CODE1[/TD]
[TD="bgcolor: transparent"]ENTITY ONE[/TD]
[TD="bgcolor: transparent"]12.90[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]22/10/2022[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]CODE1[/TD]
[TD="bgcolor: transparent"]ENTITY ONE[/TD]
[TD="bgcolor: transparent"]58.01[/TD]
[TD="bgcolor: transparent"]18/08/2020[/TD]
[TD="bgcolor: transparent"]17/08/2025[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]CODE2[/TD]
[TD="bgcolor: transparent"]ENTITY TWO[/TD]
[TD="bgcolor: transparent"]11.00[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]24/03/2019[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]CODE3[/TD]
[TD="bgcolor: transparent"]ENTITY THREE[/TD]
[TD="bgcolor: transparent"]17.51[/TD]
[TD="bgcolor: transparent"]24/06/2035[/TD]
[TD="bgcolor: transparent"]23/06/2037[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]CODE4[/TD]
[TD="bgcolor: transparent"]ENTITY FOUR[/TD]
[TD="bgcolor: transparent"]19.99[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]02/12/2022[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]CODE4[/TD]
[TD="bgcolor: transparent"]ENTITY FOUR[/TD]
[TD="bgcolor: transparent"]20.01[/TD]
[TD="bgcolor: transparent"]18/07/2022[/TD]
[TD="bgcolor: transparent"]17/07/2027[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]CODE4[/TD]
[TD="bgcolor: transparent"]ENTITY FOUR[/TD]
[TD="bgcolor: transparent"]19.99[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]05/09/2020[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]CODE4[/TD]
[TD="bgcolor: transparent"]ENTITY FOUR[/TD]
[TD="bgcolor: transparent"]20.72[/TD]
[TD="bgcolor: transparent"]12/05/2022[/TD]
[TD="bgcolor: transparent"]11/05/2027[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]CODE4[/TD]
[TD="bgcolor: transparent"]ENTITY FOUR[/TD]
[TD="bgcolor: transparent"]15.51[/TD]
[TD="bgcolor: transparent"]12/10/2025[/TD]
[TD="bgcolor: transparent"]11/10/2030[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]CODE4[/TD]
[TD="bgcolor: transparent"]ENTITY FOUR[/TD]
[TD="bgcolor: transparent"]29.98[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]31/01/2033[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]CODE5[/TD]
[TD="bgcolor: transparent"]ENTITY FIVE[/TD]
[TD="bgcolor: transparent"]38.07[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]18/06/2023[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]CODE5[/TD]
[TD="bgcolor: transparent"]ENTITY FIVE[/TD]
[TD="bgcolor: transparent"]160.00[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]23/10/2021[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]CODE5[/TD]
[TD="bgcolor: transparent"]ENTITY FIVE[/TD]
[TD="bgcolor: transparent"]62.33[/TD]
[TD="bgcolor: transparent"]23/04/2021[/TD]
[TD="bgcolor: transparent"]22/04/2024[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]CODE5[/TD]
[TD="bgcolor: transparent"]ENTITY FIVE[/TD]
[TD="bgcolor: transparent"]50.00[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]22/04/2025[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]CODE5[/TD]
[TD="bgcolor: transparent"]ENTITY FIVE[/TD]
[TD="bgcolor: transparent"]50.01[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]10/05/2019[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]CODE5[/TD]
[TD="bgcolor: transparent"]ENTITY FIVE[/TD]
[TD="bgcolor: transparent"]0.00[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]23/06/2080[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]CODE5[/TD]
[TD="bgcolor: transparent"]ENTITY FIVE[/TD]
[TD="bgcolor: transparent"]0.00[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]24/06/2080[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]CODE5[/TD]
[TD="bgcolor: transparent"]ENTITY FIVE[/TD]
[TD="bgcolor: transparent"]0.00[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]23/06/2080[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR][/TABLE]
This is my current output:
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: yellow"] External ID [/TD]
[TD="bgcolor: yellow"] Name [/TD]
[TD="bgcolor: yellow"] Amounts£ [/TD]
[TD="bgcolor: yellow"] Date1 [/TD]
[TD="bgcolor: yellow"] Date2 [/TD]
[TD="bgcolor: transparent"] CODE1 [/TD]
[TD="bgcolor: transparent"] ENTITY ONE [/TD]
[TD="width: 161, bgcolor: transparent"] £12.90 - £63.56 [/TD]
[TD="width: 136, bgcolor: transparent"] - Aug-20 [/TD]
[TD="width: 222, bgcolor: transparent"] Jun-19 - Aug-25 [/TD]
[TD="bgcolor: transparent"] CODE2 [/TD]
[TD="bgcolor: transparent"] ENTITY TWO [/TD]
[TD="width: 161, bgcolor: transparent"] £11.00 [/TD]
[TD="width: 136, bgcolor: transparent"] [/TD]
[TD="width: 222, bgcolor: transparent"] Mar-19 [/TD]
[TD="bgcolor: transparent"] CODE3 [/TD]
[TD="bgcolor: transparent"] ENTITY THREE [/TD]
[TD="width: 161, bgcolor: transparent"] £17.51 [/TD]
[TD="width: 136, bgcolor: transparent"] Jun-35 [/TD]
[TD="width: 222, bgcolor: transparent"] Jun-37 [/TD]
[TD="bgcolor: transparent"] CODE4 [/TD]
[TD="bgcolor: transparent"] ENTITY FOUR [/TD]
[TD="width: 161, bgcolor: transparent"] £15.51 - £29.98 [/TD]
[TD="width: 136, bgcolor: transparent"] - Oct-25 [/TD]
[TD="width: 222, bgcolor: transparent"] Sep-20 - Jan-33 [/TD]
[TD="bgcolor: transparent"] CODE5 [/TD]
[TD="bgcolor: transparent"] ENTITY FIVE [/TD]
[TD="width: 161, bgcolor: transparent"] £0.00 - £160.00 [/TD]
[TD="width: 136, bgcolor: transparent"] - Apr-21 [/TD]
[TD="width: 222, bgcolor: transparent"] May-19 - Jun-80 [/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
This is what I would like:
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: yellow"] External ID [/TD]
[TD="bgcolor: yellow"] Name [/TD]
[TD="bgcolor: yellow"] Amounts£ [/TD]
[TD="bgcolor: yellow"] Date1 [/TD]
[TD="bgcolor: yellow"] Date2 [/TD]
[TD="bgcolor: transparent"] CODE1 [/TD]
[TD="bgcolor: transparent"] ENTITY ONE [/TD]
[TD="width: 161, bgcolor: transparent"] £12.90 - £63.56 [/TD]
[TD="width: 136, bgcolor: transparent"] Aug-20 [/TD]
[TD="width: 222, bgcolor: transparent"] Jun-19 - Aug-25 [/TD]
[TD="bgcolor: transparent"] CODE2 [/TD]
[TD="bgcolor: transparent"] ENTITY TWO [/TD]
[TD="width: 161, bgcolor: transparent"] £11.00 [/TD]
[TD="width: 136, bgcolor: transparent"] [/TD]
[TD="width: 222, bgcolor: transparent"] Mar-19 [/TD]
[TD="bgcolor: transparent"] CODE3 [/TD]
[TD="bgcolor: transparent"] ENTITY THREE [/TD]
[TD="width: 161, bgcolor: transparent"] £17.51 [/TD]
[TD="width: 136, bgcolor: transparent"] Jun-35 [/TD]
[TD="width: 222, bgcolor: transparent"] Jun-37 [/TD]
[TD="bgcolor: transparent"] CODE4 [/TD]
[TD="bgcolor: transparent"] ENTITY FOUR [/TD]
[TD="width: 161, bgcolor: transparent"] £15.51 - £29.98 [/TD]
[TD="width: 136, bgcolor: transparent"] Oct-25 [/TD]
[TD="width: 222, bgcolor: transparent"] Sep-20 - Jan-33 [/TD]
[TD="bgcolor: transparent"] CODE5 [/TD]
[TD="bgcolor: transparent"] ENTITY FIVE [/TD]
[TD="width: 161, bgcolor: transparent"] £38.07 - £160.00 [/TD]
[TD="width: 136, bgcolor: transparent"] Apr-21 [/TD]
[TD="width: 222, bgcolor: transparent"] May-19 - Jun-80 [/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="width: 161, bgcolor: transparent"] * I'd like the number to ignore zero and take the next smallest number [/TD]
[TD="width: 136, bgcolor: transparent"] * If there is only one date in a range I would like it to ignore the blanks [/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
This is my code:
Code: 
  Sheets("Output").Select
  Range("A5").Select
      If MAXVALUE = MINVALUE Then
          RENTVALUE = Format$(MINVALUE,  "£#,##0.00")
           Else
RENTVALUE = Format$(MINVALUE,  "£#,##0.00") & " - " & Format$(MAXVALUE,  "£#,##0.00")
       End If
      ActiveCell.Offset(I, 3) = RENTVALUE
      If MAXDATE = MINDATE Then
          NEXTREVIEW = Format(MINDATE,  "mmm-yy")
           Else
NEXTREVIEW = Format(MINDATE,  "mmm-yy") & " - " & Format(MAXDATE,  "mmm-yy")
       End If
      ActiveCell.Offset(I, 4) = NEXTREVIEW
         If MAXBDATE = MINBDATE Then
          BREAKDATE = Format(MINBDATE,  "mmm-yy")
           Else
BREAKDATE = Format(MINBDATE,  "mmm-yy") & " - " & Format(MAXBDATE,  "mmm-yy")
       End If
      ActiveCell.Offset(I, 5) = BREAKDATE
 
Last edited by a moderator:
Upvote 0
Sorry my last post doesn't seem that clear -would you like me to repost? I copied it from excel. Can attach files here?
 
Upvote 0
You can't attach files here. I've tidied up the formatting a bit for you, but you haven't shown the code that actually assigns the values you use.
 
Upvote 0
I've posted the whole thing as wasn't sure what you needed! Thanks again

Code: 
Sub Extract()
                        
Dim I, J, K, STARTROW1, STARTROW2, ENDROW1, ENDROW2, COUNTTENANT As Integer

Sheets("Output").Select
Range("A10000").Select
Selection.End(xlUp).Select

ENDROW1 = ActiveCell.Row - 4
STARTROW1 = 1

Sheets("Lease Comparison").Select
Range("A10000").Select
Selection.End(xlUp).Select

ENDROW2 = ActiveCell.Row - 2
STARTROW2 = 1

'CLEARS OUT EXISTING OUTPUT SHEETS
Sheets("Output").Select
Range("D9.H41").ClearContents


For I = STARTROW1 To ENDROW1
    
    Sheets("Output").Select
    Range("A5").Select
    PROPCODE = ActiveCell.Offset(I, 0).Value
    
    MAXVALUE = 0
    MINVALUE = 99999
    MINDATE = 99999
    MAXDATE = 0
    MINBDATE = 99999
    MAXBDATE = 0
    MINEDATE = 99999
    MAXEDATE = 0
          
    If PROPCODE = "" Then
        GoTo 1000
        Else
    End If
   
    For J = STARTROW2 To ENDROW2
    
        Sheets("Lease Comparison").Select
        Range("A2").Select
        If ActiveCell.Offset(J, 0) = PROPCODE Then
        
            RENTVALUE = ActiveCell.Offset(J, 77).Value

            If RENTVALUE < MINVALUE Then
                MINVALUE = RENTVALUE
                Else
            End If

            If RENTVALUE > MAXVALUE Then
                MAXVALUE = RENTVALUE
                Else
            End If
            
            NEXTREVIEW = ActiveCell.Offset(J, 70).Value
            
            If NEXTREVIEW < MINDATE Then
                MINDATE = NEXTREVIEW
                Else
            End If
            
            If NEXTREVIEW > MAXDATE Then
                MAXDATE = NEXTREVIEW
                Else
            End If
            
            BREAKDATE = ActiveCell.Offset(J, 59).Value
            
            If BREAKDATE < MINBDATE Then
                MINBDATE = BREAKDATE
                Else
            End If
            
            If BREAKDATE > MAXBDATE Then
                MAXBDATE = BREAKDATE
                Else
            End If
            
            EXPIRYDATE = ActiveCell.Offset(J, 64).Value
            
            If EXPIRYDATE < MINEDATE Then
                MINEDATE = EXPIRYDATE
                Else
            End If
            
            If EXPIRYDATE > MAXEDATE Then
                MAXEDATE = EXPIRYDATE
                Else
            End If
     
            Else
        End If
        
    Next J
    
    
      
Sheets("Output").Select

Range("A5").Select
    
    If MAXVALUE = MINVALUE Then
        RENTVALUE = Format$(MINVALUE, "£#,##0.00")
        Else
        RENTVALUE = Format$(MINVALUE, "£#,##0.00") & " - " & Format$(MAXVALUE, "£#,##0.00")
    End If

    ActiveCell.Offset(I, 3) = RENTVALUE
        
    If MAXDATE = MINDATE Then
        NEXTREVIEW = Format(MINDATE, "mmm-yy")
        Else
        NEXTREVIEW = Format(MINDATE, "mmm-yy") & " - " & Format(MAXDATE, "mmm-yy")
    End If
    
    ActiveCell.Offset(I, 4) = NEXTREVIEW
    
       If MAXBDATE = MINBDATE Then
        BREAKDATE = Format(MINBDATE, "mmm-yy")
        Else
        BREAKDATE = Format(MINBDATE, "mmm-yy") & " - " & Format(MAXBDATE, "mmm-yy")
    End If
    
    ActiveCell.Offset(I, 5) = BREAKDATE
    
        If MAXEDATE = MINEDATE Then
        EXPIRYDATE = Format(MINEDATE, "mmm-yy")
        Else
        EXPIRYDATE = Format(MINEDATE, "mmm-yy") & " - " & Format(MAXEDATE, "mmm-yy")
    End If
    
    ActiveCell.Offset(I, 6) = EXPIRYDATE
1000
Next I


End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

T
  • Solved
VBA Pull Data from Web table into Excel
Replies
4
Views
11K
TaskyFuji
T
D
  • Question Question
How to output a dynamic range where the height of the range varies
Replies
0
Views
1K
dagda13
D
G
  • Question Question
Conditional formating where there are 2 values in a cell
Replies
3
Views
1K
Godders199
G
E
  • Question Question
Yearfrac problem
Replies
3
Views
5K
EleanorSykes1989
E
A
  • Question Question
Add Sequence numbers Based on Dates
Replies
1
Views
3K
Jeffrey Mahoney
Jeffrey Mahoney

Forum statistics

Threads
1,223,228
Messages
6,170,871
Members
452,363
Latest member
merico17

Share this page

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