Re: Need to create two macro buttons on one page with different codes
Quote from Hope123;700215Hi Nilem, Thank you so much for your assistance ...
Thank you, Annette
I'll try to be more detailed next time
Re: Need to create two macro buttons on one page with different codes
Quote from Hope123;700215Hi Nilem, Thank you so much for your assistance ...
Thank you, Annette
I'll try to be more detailed next time
Re: Filter and delete duplicates
Hi Angadpatil,
try it
Sub ertert()
Application.ScreenUpdating = False
With Range("B3").CurrentRegion
.Sort Key1:=.Cells(1, 3), Order1:=xlAscending, _
Key2:=.Cells(1, 17), Order2:=xlAscending, Header:=xlYes
With .Offset(, -1).Resize(, 1)
.FormulaR1C1 = "=IF(COUNTIF(RC[3]:R" & _
Cells(Rows.Count, 4).End(xlUp).Row & "C4,RC[3])>1,"""")"
On Error Resume Next
.SpecialCells(xlCellTypeFormulas, xlTextValues).EntireRow.Delete
On Error GoTo 0
.ClearContents
End With
End With
Application.ScreenUpdating = True
End Sub
Display More
Re: Need to create two macro buttons on one page with different codes
Hi Hope123,
maybe so (see attached file)
Re: Searching a list of names and add it if not found.
Hi rtgjeg,
maybe something like this
Private Sub ComboBox1_AfterUpdate()
With Me.ComboBox1
If .MatchFound = False Then Cells(Rows.Count, 1).End(xlUp)(2, 1).Value = .Value
End With
End Sub
see also a similar thread
Re: Automatically create a list of column contents based on criteria
Here's your file. I just moved the function from the module sheet to a standard module ("Module5")
Re: Automatically create a list of column contents based on criteria
Hi danielstone121,
could you attach the file in which the error occurs
Re: Filter and delete
Hi angadpatil,
also try it
Sub ertert()
Dim x, i&, k&: Application.ScreenUpdating = False
With Sheets("Sheet2")
x = .Range("A1", .Cells(Rows.Count, 1).End(xlUp)).Value
End With
With CreateObject("Scripting.Dictionary")
.CompareMode = 1
For i = 1 To UBound(x)
.Item(x(i, 1)) = 1
Next i
With Sheets("incidents")
x = .Range("F1", .Cells(Rows.Count, 6).End(xlUp)).Value
End With
For i = 1 To UBound(x)
If .Exists(x(i, 1)) Then x(i, 1) = 0: k = k + 1
Next i
End With
With Sheets("incidents").Range("A1").CurrentRegion
.Columns(6).Value = x
.Sort Key1:=.Cells(1, 6), Order1:=xlAscending, Header:=xlYes
.Offset(1).Resize(k).EntireRow.Delete
End With
Application.ScreenUpdating = True
End Sub
Display More
Re: Automatically create a list of column contents based on criteria
Hi danielstone121,
maybe UDF
Function Daniel(rngAttendance As Range, rngDates As Range) As String
Dim x, i As Long, s As String: x = rngAttendance.Value
For i = 1 To UBound(x, 2)
If x(1, i) = vbNullString Or x(1, i) = "WR" Then s = s & ", " & rngDates(1, i)
Next
Daniel = Mid(s, 3)
End Function
In the worksheet, it will look like
=Daniel(V2:AC2;$V$1:$AC$1)
Re: 2 criteria Multiples columns search returning first columns
Hi flix,
maybe so (see attached file)
Re: Find any dates in a string?
Quote from stildawn;695574...So year in full, year in 2 digit, year first, year last etc. ...
I think in this case, can not do without manual input. Something like
Re: Convert into editable data
Hi Howard,
There is a picture (not grouped form) in your second file. And in this case I think you need not an Excel, but something like FineReader. Sorry
Re: Auto populate date to seperate tables in one sheet
Hipaeych,
maybe so
Re: Separate cell with "Comma" in different row & multiple each row "n" times
Hi arun_ebl,
try it
Sub ertert()
Dim x, y(), i&, j&, k&, sp2, sp4, sp5
x = Range("A1").CurrentRegion.Value
ReDim y(1 To UBound(x, 2), 1 To UBound(x, 1))
For i = 1 To UBound(x)
sp2 = Split(x(i, 2), ","): sp4 = Split(x(i, 4), ","): sp5 = Split(x(i, 5), ",")
For j = 0 To UBound(sp2)
k = k + 1
If k > UBound(y, 2) Then ReDim Preserve y(1 To UBound(x, 2), 1 To UBound(y, 2) * 1.5)
y(1, k) = x(i, 1): y(2, k) = sp2(j)
y(3, k) = x(i, 3): y(4, k) = sp4(j)
y(5, k) = sp5(j)
Next j
Next i
Range("G1").Resize(k, 5).Value = Application.Transpose(y())
End Sub
Sub rtyrty()
Dim x, y(), i&, j&, k&, n&
n = Application.InputBox("Enter n", , 3, , , , , 1)
If n = False Then Exit Sub
x = Range("G1").CurrentRegion.Value
ReDim y(1 To UBound(x, 1) * n, 1 To UBound(x, 2))
For i = 1 To UBound(x)
For j = 1 To n
k = k + 1
y(k, 1) = x(i, 1): y(k, 2) = x(i, 2): y(k, 3) = x(i, 3)
y(k, 4) = x(i, 4): y(k, 5) = x(i, 5)
Next j
Next i
Range("M1").Resize(k, 5).Value = y()
End Sub
Display More
Re: Macro to delete partial cell content and sum all pertinent values in an unique ro
Sorry, there is no Dictionary in the Mac operating system. Ignore my code.
Re: Macro to delete partial cell content and sum all pertinent values in an unique ro
You are using a Mac or Windows?
Re: Macro to delete partial cell content and sum all pertinent values in an unique ro
Hi evt,
try it
Sub ertert()
Dim x, i&, j&, k&, s$
x = Range("A1:D" & Cells(Rows.Count, 1).End(xlUp).Row).Value
With CreateObject("Scripting.Dictionary")
.CompareMode = 1
For i = 1 To UBound(x)
If InStr(x(i, 1), "@") Then
s = Split(Split(x(i, 1), "@")(1), ".")(0)
If .Exists(s) Then
k = .Item(s)
x(k, 2) = x(k, 2) + x(i, 2)
x(k, 3) = x(k, 3) + x(i, 3)
x(k, 4) = x(k, 4) + x(i, 4)
Else
j = j + 1: .Item(s) = j
x(j, 1) = s
x(j, 2) = x(i, 2)
x(j, 3) = x(i, 3)
x(j, 4) = x(i, 4)
End If
End If
Next i
End With
Range("F1:I1").Resize(j).Value = x
End Sub
Display More