Need to get first 2 Values of a Workbook Name

Eric Penfold

Active Member
Joined
Nov 19, 2021
Messages
431
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
Can`t seem to get the VBA to get first 2 Words from a File Name.

VBA Code:
        If UCase(wb.Name) Like "Purchase Orders*" Then
            .Range("E2" & LRow).Value = Date
            .Range("E2" & LRow).NumberFormat = "dd/mm/yyyy"
            End If

Sub Format_Cells()

    Dim wb     As Workbook
    Dim ws     As Worksheet
    Dim LRow   As Long, i As Long
    Dim DelRng As Range
    Dim TxString As String, Result() As String
    Dim wc     As Integer

    Set wb = ActiveWorkbook
    Set ws = wb.Sheets("Sheet")
    LRow = Cells(Rows.Count, 1).End(xlUp).Row
    Set DelRng = ws.Range("A2:E" & LRow)
    
    With Application
        .ScreenUpdating = False
        .DisplayAlerts = False
    End With
    
    DelRng.Cells.UnMerge
    
    With ws
        For i = LRow To 1 Step -1
            If .Range("A" & i).Value = "" Then
                .Range("A" & i).EntireRow.Delete
            End If
        Next i
        
        For i = 1 To LRow
            If IsNumeric(.Cells(i, 6).Value) Then
                .Cells(i, 6).Insert Shift:=xlToRight
            End If
            
            .Cells(i, 5).Value = .Cells(i, 5).Value & " " & .Cells(i, 6).Value
        Next i
        
        .Range("E1").Replace What:="*", _
                              Replacement:="Due Date", MatchCase:=True
        
        ws.Columns("A").HorizontalAlignment = Excel.Constants.xlCenter
        ws.Columns("C").HorizontalAlignment = Excel.Constants.xlCenter
        ws.Columns("E").HorizontalAlignment = Excel.Constants.xlCenter
        
        .Columns(9).Delete
        .Columns(8).Delete
        .Columns(7).Delete
        .Columns(6).Delete
        
        If UCase(wb.Name) Like "Purchase Orders*" Then
            .Range("E2" & LRow).Value = Date
            .Range("E2" & LRow).NumberFormat = "dd/mm/yyyy"
            End If
            
        End With
        
        For i = 1 To Cells.SpecialCells(xlLastCell).Rowb
            If Cells(i, 1) <> vbNullString Then
                If Cells(i, 1) = Cells(i + 1, 1) Then
                    Cells(i + 1, 1).EntireRow.Delete
                    i = i - 1
                End If
            End If
        Next i
        
        With Application
            .ScreenUpdating = True
            .DisplayAlerts = True
        End With
        
End Sub
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
As you are using UCASE have you tried:
VBA Code:
    If UCase(wb.Name) Like "PURCHASE ORDERS*" Then
        .Range("E2" & LRow).Value = Date
        .Range("E2" & LRow).NumberFormat = "dd/mm/yyyy"
    End If

Or
VBA Code:
    If Left(UCase(wb.Name), 15) = "PURCHASE ORDERS" Then
        .Range("E2" & LRow).Value = Date
        .Range("E2" & LRow).NumberFormat = "dd/mm/yyyy"
    End If
 
Upvote 0
Thanks didn`t need the UCase. Sorry about that.
But it now works but for some reason the Changing the cells in the column does not work??
 
Upvote 0
I am not sure that this part is doing what you think it will:
VBA Code:
("E2" & LRow)

Try instead:
VBA Code:
("E2:E" & LRow)


As it was, if say LRow was 20 then your code was changing cell E220
 
Upvote 0

Forum statistics

Threads
1,223,894
Messages
6,175,254
Members
452,623
Latest member
Techenthusiast

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