Formatting in excel

[smithy0705]

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

12.9000299280694 - 63.5578583765112

<colgroup><col width="268" style="width: 201pt; mso-width-source: userset; mso-width-alt: 11434;"> <tbody> </tbody>

to £12.90 - £63.58

Please can anyone help.
Thanks so much in advance
Joanne

 

[RoryA]

What is the current code that produces that output?

 

[smithy0705]

Sheets("Output").Select
Range("A5").Select

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

ActiveCell.Offset(I, 3) = RENTVALUE

 

[RoryA]

You can use Format

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

 

[smithy0705]

Thank you so much for this amazing!

Can you also help to remove/ignore any zeros?

This is my raw data:

38.07
160.00
62.33
50.00
50.01
0.00
0.00
0.00

<colgroup><col width="133" style="width: 100pt; mso-width-source: userset; mso-width-alt: 5674;"> <tbody> </tbody>

Thank you

 

[RoryA]

What do you expect the output to be if one of the values is 0 and the other isn't?

 

[smithy0705]

Thank you for responding and helping me I'm very grateful!

This is my raw data
External ID	Name	Amounts£	Date1	Date2
CODE1	ENTITY ONE	26.96		06/09/2022
CODE1	ENTITY ONE	18.47		23/06/2019
CODE1	ENTITY ONE	63.56		22/07/2024
CODE1	ENTITY ONE	12.90		22/10/2022
CODE1	ENTITY ONE	58.01	18/08/2020	17/08/2025
CODE2	ENTITY TWO	11.00		24/03/2019
CODE3	ENTITY THREE	17.51	24/06/2035	23/06/2037
CODE4	ENTITY FOUR	19.99		02/12/2022
CODE4	ENTITY FOUR	20.01	18/07/2022	17/07/2027
CODE4	ENTITY FOUR	19.99		05/09/2020
CODE4	ENTITY FOUR	20.72	12/05/2022	11/05/2027
CODE4	ENTITY FOUR	15.51	12/10/2025	11/10/2030
CODE4	ENTITY FOUR	29.98		31/01/2033
CODE5	ENTITY FIVE	38.07		18/06/2023
CODE5	ENTITY FIVE	160.00		23/10/2021
CODE5	ENTITY FIVE	62.33	23/04/2021	22/04/2024
CODE5	ENTITY FIVE	50.00		22/04/2025
CODE5	ENTITY FIVE	50.01		10/05/2019
CODE5	ENTITY FIVE	0.00		23/06/2080
CODE5	ENTITY FIVE	0.00		24/06/2080
CODE5	ENTITY FIVE	0.00		23/06/2080

<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;">

This is my current output:

External ID	Name	Amounts£	Date1	Date2
CODE1	ENTITY ONE	£12.90 - £63.56	- Aug-20	Jun-19 - Aug-25
CODE2	ENTITY TWO	£11.00		Mar-19
CODE3	ENTITY THREE	£17.51	Jun-35	Jun-37
CODE4	ENTITY FOUR	£15.51 - £29.98	- Oct-25	Sep-20 - Jan-33
CODE5	ENTITY FIVE	£0.00 - £160.00	- Apr-21	May-19 - Jun-80

This is what I would like:

External ID	Name	Amounts£	Date1	Date2
CODE1	ENTITY ONE	£12.90 - £63.56	Aug-20	Jun-19 - Aug-25
CODE2	ENTITY TWO	£11.00		Mar-19
CODE3	ENTITY THREE	£17.51	Jun-35	Jun-37
CODE4	ENTITY FOUR	£15.51 - £29.98	Oct-25	Sep-20 - Jan-33
CODE5	ENTITY FIVE	£38.07 - £160.00	Apr-21	May-19 - Jun-80
		* I'd like the number to ignore zero and take the next smallest number	* If there is only one date in a range I would like it to ignore the blanks

This is my 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

 

[smithy0705]

Sorry my last post doesn't seem that clear -would you like me to repost? I copied it from excel. Can attach files here?

 

[RoryA]

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.

 

[smithy0705]

I've posted the whole thing as wasn't sure what you needed! Thanks again

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