Fill in the start date and end date with each list name with vba

[roykana]

Dear All Master ,

Please help me to fill in the start and end dates that have been specified in the vba sheet in column B that I marked the color based on the list of names in the vba sheet in column B that I marked the color. The result is in sheet 2 in column A & B which I marked green. I want to be able to use VBA so that it is not manual and just click the button that has the VBA sheet. In sheet 1 in the c & d column that I marked in red I want to be able to use the formula if an error (#value) occurs, then the format of the region is changed to Indonesian.
This is my link : ABSENSI 2004.xlsm

Thanks

kana

 

[mse330]

Hi

Try the below code to put your data from sheet VBA to sheet2 ... I didn't understand what you need to accomplish for sheet1 though

Sub Button1_Click()

Dim a, Cnt&, sDate&, eDate&, s, i&

With Sheets("VBA")
   sDate = .Range("B1")
   eDate = .Range("B2")
   Cnt = eDate - sDate + 1
   a = .Range("D2", .Range("D" & Rows.Count).End(3))
End With

i = 2
With Sheets("Sheet2")
   For Each s In a
      .Cells(i, "A").Resize(Cnt) = s
      .Cells(i, "B").Resize(Cnt) = Evaluate("row(1:" & Cnt & ")+" & sDate - 1 & "")
      i = i + Cnt
   Next
End With

End Sub

 

[roykana]

Hi

Try the below code to put your data from sheet VBA to sheet2 ... I didn't understand what you need to accomplish for sheet1 though

Sub Button1_Click()

Dim a, Cnt&, sDate&, eDate&, s, i&

With Sheets("VBA")
   sDate = .Range("B1")
   eDate = .Range("B2")
   Cnt = eDate - sDate + 1
   a = .Range("D2", .Range("D" & Rows.Count).End(3))
End With

i = 2
With Sheets("Sheet2")
   For Each s In a
      .Cells(i, "A").Resize(Cnt) = s
      .Cells(i, "B").Resize(Cnt) = Evaluate("row(1:" & Cnt & ")+" & sDate - 1 & "")
      i = i + Cnt
   Next
End With

End Sub

Dear sir

thank you for the solution you made. can you add a VBA CODE with progress status and success status.
for sheet 1 I want the results that I marked red with the formula. for new state, fill in C / Out means the second or last time or if a single or one time appears C / Out. You can see in column i that counts with the count if formula.

Thanks
kana

 

[mse330]

Hi kana,

In your sample file, Sheet1 column B has an invalid date time format 21/03/2020 07.55 & therefore, your count if formula in column 'i' returns 1 for all examples. You have 'state' in column C which is always C/In & in some instances of column D (New State), you have C/Out which I can't link to the formula of column i ?

Sorry, it is still not clear to me what you're trying to do or what macro should I do to fulfil your requirements

 

[roykana]

Dear sir,
can you add a VBA CODE with message box, progress status and success status. I want to ask if I use column d, that is, list names of more than one column such as id and others, how about the vba code ?. I'm a beginner, I want to learn a lot from you. For the formula, I passed it first because I wasn't in front of the laptop and I replied using my cellphone.
Thankslater I make sure to explain in detail about c / in and c / out maybe you have the best solution
thanks
Kana