Hello. I want help extracting the name of the product from column C, provided there is the word "Restaurant*") The date from the H column
And it is repeated along the column down to the second condition so that it is extracted and repeated in the same way. The problem is that I used this code, it does a good job, but it is very slow, because the file consists of hundreds of rows and invoices, and it always extracts names and dates from the first invoice, even though it is not empty. Can I modify it and help me in another code that does the same task, but it starts when it finds the invoice is empty
And it is repeated along the column down to the second condition so that it is extracted and repeated in the same way. The problem is that I used this code, it does a good job, but it is very slow, because the file consists of hundreds of rows and invoices, and it always extracts names and dates from the first invoice, even though it is not empty. Can I modify it and help me in another code that does the same task, but it starts when it finds the invoice is empty
VBA Code:
Sub Remplir_tout_all()
Dim nmax&, a$(), derlig&, tablo, i&, n&, C As Range, dat$, h&
Dim rng As Range
Set ST = Sheet1
lr = ST.Range("a" & Rows.Count).End(xlUp).Row
With Sheet1
nmax = Application.CountIf(.[c:c], "Restaurant*")
ReDim a(nmax)
derlig = .Range("C" & .Rows.Count).End(xlUp).Row + 1
a(nmax) = "c" & derlig
tablo = .Range("c1:c" & derlig)
For i = 1 To derlig
If Trim(tablo(i, 1)) Like "Restaurant*" Then a(n) = "c" & i: n = n + 1
Next i
Application.ScreenUpdating = False
Range("a4:b" & lr).ClearContents
.[A:B].HorizontalAlignment = xlCenter
For n = 0 To UBound(a) - 1
Set C = .Range(a(n))
dat = Mid(Trim(C(5, 6)), 11, 10)
h = .Range(a(n + 1)).Row - 1 - C(5).Row
If h > 0 Then
If IsDate(dat) Then C(6, -1).Resize(h) = CDate(dat)
C(6, -0).Resize(h) = C(2)
End If
Next n
End With
End Sub
