Update value in a column from another worksheet depending on the date in a range

[RaghuPrabhu]

Our new pay system at work went live in August 2017.
This is the new pay calendar (called PayPeriods)

Start Date	End Date	Pay period
10 Aug 17	23 Aug 17	PP0001
24 Aug 17	06 Sep 17	PP0002
07 Sep 17	20 Sep 17	PP0003
21 Sep 17	04 Oct 17	PP0004
05 Oct 17	18 Oct 17	PP0005
19 Oct 17	01 Nov 17	PP0006

This is the table for members (called MemberList)

Name	Due Date	Amount	Processed in Pay Cal
Raghu	15 Jul 17	$248.00	PP0001
Vim	20 Jul 17	$354.00	PP0001
Abhi	10 Aug 17	$954.00	PP0001
Neelima	23 Aug 17	$134.00	PP0001
Raghu	14 Sep 17	$134.00	PP0003
Vim	21 Sep 17	$524.00	PP0004
Abhi	06 Oct 17	$332.00	PP0005
Neelima	20 Oct17	$158.00	PP0006
Raghu	06 Sep16	$456.00	PP0002
Vim	19 Sep 17	$159.00	PP0003
Abhi	03 Oct 17	$357.00	PP0004
Neelima	18 Oct 17	$852.00	PP0005

I want to update the Processed in Pay Cal column with the pay periods.
Anything with a due date on or 23 AUG 17 to be processed in PP0001 and the rest in the appropriate pay calendar. I need help with this codeing please.

Sub updateColunm4()
If duedate <= 23Aug17 then
Update column 4 to PP0001
Else
Update column 4 to the appropriate pay period
End if
End sub
Thank you

Regards

Raghu

[werafa]

Hi Raghu

one option is to use the 'case' command

it uses the logic

Select Case test_expression   
   Case condition_1
      result_1
   Case condition_2
      result_2
   ...
   Case condition_n
      result_n
 [ Case Else
      result_else ]
End Select

there are many good tutorials on google

[RaghuPrabhu]

Hi Raghu

one option is to use the 'case' command

it uses the logic

Select Case test_expression   
   Case condition_1
      result_1
   Case condition_2
      result_2
   ...
   Case condition_n
      result_n
 [ Case Else
      result_else ]
End Select

there are many good tutorials on google

Thanks werafa will try but may need help. I am a novice.

Regards
Raghu

[werafa]

set up each of your dates in the case statement

eg,

dim endDate as date
dim payCode as string

endDate = .......

select case endDate
   Case is <= 23 Aug 17
      payCode = "PP0001"
   Case is <= 06 Sep 17
      payCode = "PP0002"
......
end select

Excel will scan the list from top to bottom until the first true condition is found, and then execute the subsequent line(s)
you will need to confirm that the date formatting I've used is acceptable.