Consulting

Results 1 to 7 of 7

Thread: VBA REPLACE CURRENT CELL WITH CURRENT MONTH AND TEXT

  1. #1

    VBA REPLACE CURRENT CELL WITH CURRENT MONTH AND TEXT

    Hi!

    I can't figure out the code to replace
    ***"Remaining Forecast"
    ***With "March (current month)Remaining Forecast" = Final Cell Value "March Remaining Forecast"
    ***Fill Cell in Red

    ***"Current+1 Forecast"
    ***With "April (Current Month +1) Forecast" = Final Cell Value "April Forecast"
    ***Fill Cell in Yellow

    Thank you!

  2. #2
    VBAX Expert
    Joined
    May 2016
    Posts
    604
    Location
    Your question is very unclear but I hope this helps:

    [vba]
    sub test
    monthtxt = MonthName(Month(Now())) & " Remaining Forecast"
    MsgBox (monthtxt)
    monthtxt1 = MonthName(1 + Month(Now())) & " Current + 1 Forecast"
    MsgBox (monthtxt1)
    end sub
    [/vba]

  3. #3
    Quote Originally Posted by offthelip View Post
    Your question is very unclear but I hope this helps:

    [vba]
    sub test
    monthtxt = MonthName(Month(Now())) & " Remaining Forecast"
    MsgBox (monthtxt)
    monthtxt1 = MonthName(1 + Month(Now())) & " Current + 1 Forecast"
    MsgBox (monthtxt1)
    end sub
    [/vba]
    I'm sorry offthelip, I'm actually creating a header replacing current header from the system.
    Now, Cell Q1 has Text "Remaining Forecast", the goal is to replace cell string with this "Remaining March Forecast". March represents current month.
    2nd, Cell T1 has Text "Current +1 Forecast", again will like to replace cell string with "April Forecast". April represents current month + 1 month, in other words next month. Can I use your syntax this way?

    Cells.Replace what:="Remaining Forecast", Replacement:="MonthName(Month(Now())) & " Remaining Forecast""
    ??

    Let me know.

    Thank you for your response by the way.

  4. #4
    VBAX Expert
    Joined
    May 2016
    Posts
    604
    Location
    I think I understand what you want to do:
    For the first cell put this equatiojn in:

    ="Remaining " & TEXT(NOW(),"mmmm") & " Forecast"

    for the second cell put this equation in:



    =TEXT(EDATE(NOW(),1),"mmmm") &" Forecast"

    you don't need VBa at all it can be done by excel functions

  5. #5
    Administrator
    VP-Knowledge Base
    VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    To colour Offthelip's solution use conditional formatting with formula (address adjusted to suit) such as:
    =A1="Remaining " & TEXT(NOW(),"mmmm") & " Forecast"
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.


    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'

  6. #6
    Quote Originally Posted by mdmackillop View Post
    To colour Offthelip's solution use conditional formatting with formula (address adjusted to suit) such as:
    =A1="Remaining " & TEXT(NOW(),"mmmm") & " Forecast"
    I think we are good.

    It worked.

    Thank you all.

    Will submit my final code for feedback.

  7. #7
    Quote Originally Posted by EdwardOcampo View Post
    I think we are good.

    It worked.

    Thank you all.

    Will submit my final code for feedback.
    Good morning All,

    Feedback will be appreciated.
    It took a little less than a minute to run.

    Thank you.
    Sub LSRSetUp()
    ActiveSheet.Name = "Low Stock Report"
    Cells.Replace what:="2nd Item Number", Replacement:="Item#"
    Cells.Replace what:="ABC 1 Sls", Replacement:="ABC"
    Cells.Replace what:="Out of Stock", Replacement:="Available"
    Range("AH2").Value = "Comment T1"
    Range("AN2").Value = "Comment T2"
    Range("AT2").Value = "Comment T3"
    Range("AZ2").Value = "Comment T4"
    Range("BF2").Value = "Comment T5"
    Range("BL2").Value = "Comment T6"
    Range("BS2").Value = "Comment T7"
    Cells.Replace what:="Remaining Forecast", Replacement:="Remaining " & MonthName(Month(Now())) & " Forecast"
    Cells.Replace what:="Cur Shortage", Replacement:=MonthName(Month(Now())) & " Shortage"
    Cells.Replace what:="Cur+1 Forecast", Replacement:=MonthName(1 + Month(Now())) & " Forecast"
    Cells.Replace what:="Cur+1 Shortage", Replacement:=MonthName(1 + Month(Now())) & " Shortage"
    Cells.Replace what:="Cur+2 Forecast", Replacement:=MonthName(2 + Month(Now())) & " Forecast"
    Cells.Replace what:="Cur+2 Shortage", Replacement:=MonthName(2 + Month(Now())) & " Shortage"
    Cells.Replace what:="Cur+3 Forecast", Replacement:=MonthName(3 + Month(Now())) & " Forecast"
    Cells.Replace what:="Cur+3 Shortage", Replacement:=MonthName(3 + Month(Now())) & " Shortage"
    Cells.Replace what:="Cur+4 Forecast", Replacement:=MonthName(4 + Month(Now())) & " Forecast"
    Cells.Replace what:="Cur+4 Shortage", Replacement:=MonthName(4 + Month(Now())) & " Shortage"
    Set Rg1 = Range("N1:N1,R1:R1,U1:U1,W1:W1,Z1:Z1,AB1:AB1")
    Rg1.Interior.ColorIndex = 3
    Rg1.Font.Bold = True
    Set Rg2 = Range("Q1:Q1,T1:T1,V1:V1,Y1:Y1,AA1:AA1")
    Rg2.Interior.ColorIndex = 6
    Range("AC1:AH1,AU1:AZ1").Interior.ColorIndex = 15
    Range("AI1:AN1").Interior.ColorIndex = 40
    Range("AO1:AT1").Interior.ColorIndex = 5
    Range("BA1:BF1,BM1:BS1").Interior.ColorIndex = 48
    Range("BG1:BL1").Interior.ColorIndex = 10
    With ActiveWindow
        .SplitColumn = 2
        .SplitRow = 1
        .FreezePanes = True
    End With
    Dim Range1 As Range
    Set Range1 = Range("N2:N5000")
    For Each cell In Range1
        If cell.Value < 1 Then
        cell.Interior.ColorIndex = 3
        
        End If
    Next
    Set Range1 = Range("r2:r5000")
    For Each cell In Range1
        If cell.Value < 1 Then
        cell.Interior.ColorIndex = 3
        
        End If
    Next
    Set Range1 = Range("u2:u5000")
    For Each cell In Range1
        If cell.Value < 1 Then
        cell.Interior.ColorIndex = 3
        
        End If
    Next
    Set Range1 = Range("w2:w5000")
    For Each cell In Range1
        If cell.Value < 1 Then
        cell.Interior.ColorIndex = 3
        
        End If
    Next
    Set Range1 = Range("z2:z5000")
    For Each cell In Range1
        If cell.Value < 1 Then
        cell.Interior.ColorIndex = 3
        
        End If
    Next
    Set Range1 = Range("AB2:AB5000")
    For Each cell In Range1
        If cell.Value < 1 Then
        cell.Interior.ColorIndex = 3
        
        End If
    Next
    Dim Range2 As Range
    Set Range2 = Range("N2:AB5000")
    For Each cell In Range2
        If cell.Interior.ColorIndex = 3 Then
        cell.Font.Bold = True
        
        End If
    Next
    With ActiveSheet
        .AutoFilterMode = False
        .Range("A1:BW1").AutoFilter
    End With
    Range("B:B").Replace "'", ""
    End Sub

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •