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

MarkRandall

New Member
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

 Tue Wed Thu Fri Sat 8-Sep 9-Sep 10-Sep 11-Sep 12-Sep employee 1 Trg Trg Trg AR employee 2 DP Trg employee 3 employee 4 Trg Trg Trg AR employee 5 employee 6 employee 7 DP Trg employee 8 Trg Trg Trg AR 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
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
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
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
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
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
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
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

Trebor76

Well-known Member
It works for column E but I need it to work for the entire spreadsheet
What do you mean

thanks Michael

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

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...