Populating 1, 2, or 3 cells to the left with text of any cell that contains a specific value (test also)

MarkRandall

New Member
Joined
Sep 15, 2015
Messages
11
Hi all,

hoping to get some help with this.

I am establishing an availability table in Excel. I have dates listed by day across the top of the spreadsheet and people listed down the left. In the table I have the date that the individuals arrives on shift marked as AR and the day that they leave shift as DP.

I would like to use some excel magic to populate the three cells to the immediate left of the cells that contain AR with the text TRG (to indicate that they are available to attend training). Once this is done, I can filter by dates to see who is available across the entire company.

Then i would like to do the same to indicate that an individual is available for training for the 3 days after they depart (DP)

Small mock up below

TueWedThuFriSat
8-Sep9-Sep10-Sep11-Sep12-Sep
employee 1TrgTrgTrgAR
employee 2 DP Trg
employee 3
employee 4TrgTrgTrgAR
employee 5
employee 6
employee 7 DP Trg
employee 8TrgTrgTrgAR
employee 9
employee 10 DP
employee 11 AR
employee 12
employee 13 AR
employee 14

<colgroup><col><col span="5"></colgroup><tbody>
</tbody>

Appreciate all help

cheers
Mark
 

DebugGalpin

Board Regular
Joined
Jun 29, 2011
Messages
175
Macro or formula Mark?

Macro would be easier to write

Sub Test1

s=4 ' Looks like your data starts in row 4 so you may want to change that
Do until Cells(s,1) = vbnullstring
Dim CheckAR as variant
Err.Clear
On error resume next
CheckAR = Application.Worksheetfunction.Match("AR", Rows(s),0)
If Err.Number = 0 then
Cells(s,CheckAR-1) = "TRG"
Cells(s,CheckAR-2) = "TRG"
Cells(s,CheckAR-3) = "TRG"
Else
End if

Dim CheckDR as variant
Err.Clear
On error resume next
CheckDR = Application.Worksheetfunction.Match("DR", Rows(s),0)
If Err.Number = 0 then
Cells(s,CheckDR+1) = "TRG"
Cells(s,CheckDR+2) = "TRG"
Cells(s,CheckDR+3) = "TRG"
Else
End if
s=s+1
Loop
 

Trebor76

Well-known Member
Joined
Jul 23, 2007
Messages
4,467
Hi Mark,

Welcome to MrExcel!!

This macro will do the trick:

Code:
Option Explicit
Sub Macro2()

    Const lngStartRow As Long = 4 'Starting (static) row number for the data. Change to suit if necessary.
    
    Dim lngMyRow As Long
    Dim lngLastRow As Long
    
    On Error Resume Next 'Account for there being no data on the tab.
        lngLastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    On Error GoTo 0
    
    If lngLastRow < lngStartRow Then
        MsgBox "There is no data from the defined start row!!", vbExclamation
        Exit Sub
    End If
    
    Application.ScreenUpdating = False
    
    For lngMyRow = lngStartRow To lngLastRow
        Select Case StrConv(Range("E" & lngMyRow), vbUpperCase) 'Assumes the 'AR' and 'AD' flags are in Col. E. Change to suit if necessary.
            Case Is = "AR"
                Range("B" & lngMyRow & ":D" & lngMyRow).Value = "Trg" 'Populates colums B to D with 'Trg' if the text in Col. E is 'AR'. Change to suit if necessary.
            Case Is = "DP"
                Range("F" & lngMyRow & ":H" & lngMyRow).Value = "Trg" 'Populates colums F to H with 'Trg' if the text in Col. E is 'DP'. Change to suit if necessary.
        End Select
    Next lngMyRow
    
    Application.ScreenUpdating = True
    
    MsgBox "Process is now complete.", vbInformation

End Sub
Regards,

Robert
 

MarkRandall

New Member
Joined
Sep 15, 2015
Messages
11
thanks DebugGalpin, I'll give it a try now.

Only slightly familiar with macros and coding. Will let you know how I go.

cheers
 

MarkRandall

New Member
Joined
Sep 15, 2015
Messages
11
No luck

Here is what I did

Saved worksheet as macro enabled
went to DEveloper tab and opened VB
Inserted a new module under sheet 1
pasted code into new window
saved
enabled all macros
run the macro test one and got the following error msg.

Complie error
Expexted end sub
run it again and then I get

Can't execute code in break mode


Have I done something wrong or missed something?

cheers
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
18,180
Office Version
2013
Platform
Windows
When a code fails or errors, it stays active until you press the stop button.
Otherwise I'd suggest using Roberts code !
 

MarkRandall

New Member
Joined
Sep 15, 2015
Messages
11
Hi Robert,

Thanks for the help although I am not quite there. Is there a limit on the columns that this macro is applied to.

It works for column E but I need it to work for the entire spreadsheet

What can I change to make it work?

Just a little more....I read you comments in green but not sure how to apply it to a range of cells to be specific, I need the macro to work from cells F4 through FB371

cheers
Mark
 
Last edited:

Trebor76

Well-known Member
Joined
Jul 23, 2007
Messages
4,467
DebugGalpin's code is missing the text...

End Sub

...as the very last line of code

You can do this via a formula i.e. put this formula into cell B4 and fill down and across as required...

=IF($E4="AR","Trg","")

...and put this formula into cell F4 and fill down and across as required:

=IF($E4="DP","Trg","")

HTH

Robert
 

Forum statistics

Threads
1,081,981
Messages
5,362,533
Members
400,679
Latest member
alecalec202

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top