Posts by BaraaKhalil
-
-
-
-
-
Re: format shapes depending on linked cells.
Cross-Post at this link
https://www.excelforum.com/excel-programm…nked-cells.html -
Re: format shapes depending on linked cells.
Hello
Can you upload sample of your workbook? -
-
-
Re: Find file within directory
You're welcome. Glad I can offer some help
You can play with the code ..
As for this string for example : 3456_1234_7643 .. After split by underscore it will be three parts : first part is 3456 and this is referred by using (0) as the first index
then the second part is 1234 and its index will be 1 .. And the third part is 7643 which its index is 2
Hope this helps you -
Re: Find file within directory
Hello
I have no experience of network drive and how to set path for it but the following code may help you outCode
Display MoreSub LoopThroughClosedWBs() Dim wbk As Workbook Dim folderPath As String Dim fileName As String Dim strCrit As String Dim strWb As String Dim x As Long folderPath = ThisWorkbook.Path & "\Files\" fileName = Dir(folderPath & "*.xls") strCrit = ThisWorkbook.Sheets("Sheet1").Range("A1").Value Application.ScreenUpdating = False Application.Calculation = xlManual Do While fileName <> "" If InStr(fileName, strCrit) > 0 Then If Split(Split(fileName, ".")(0), "_")(0) > x Then x = Split(Split(fileName, ".")(0), "_")(0) strWb = fileName End If End If fileName = Dir() Loop Set wbk = Workbooks.Open(folderPath & strWb) 'wbk.Close SaveChanges:=True Application.Calculation = xlAutomatic Application.ScreenUpdating = True MsgBox "Done ...", 64 End Sub
-
Re: Import txt file into excel
Hello
Have a look at this file -
Re: Retrieve all rows of duplicate IDs with a different create date.
Hello
Is it a problem for you to sort the data according to ID column? this way will enable you to gather each similar IDs in sequence ..
Generally try this codeCode
Display MoreSub HighlightDuplicates() Dim dic As Object Dim arr As Variant Dim i As Long Application.ScreenUpdating = False Set dic = CreateObject("Scripting.Dictionary") With Range("A1").CurrentRegion .Interior.Color = xlNone arr = .Value For i = 2 To UBound(arr, 1) If arr(i, 2) <> "" Then dic(arr(i, 2)) = dic(arr(i, 2)) + 1 If dic(arr(i, 2)) > 1 Then .Cells(i, 1).Resize(1, 4).Interior.Color = vbCyan End If End If Next i End With Application.ScreenUpdating = True End Sub
-
Re: Match Two Columns then Sum
You're welcome. Glad I can offer some help
-
Re: Match Two Columns then Sum
Hello
Try this codeCode
Display MoreSub Test() Dim d As Object Dim i As Long Dim arr, s, v() Set d = CreateObject("scripting.dictionary") arr = [A1].CurrentRegion.Value ReDim v(UBound(arr), 1 To 3) For i = 1 To UBound(arr) s = arr(i, 1) & "_" & arr(i, 2) If Not d.exists(s) Then d(s) = d.Count v(d(s), 1) = arr(i, 1) v(d(s), 2) = arr(i, 2) End If v(d(s), 3) = v(d(s), 3) + arr(i, 3) Next i [F1].Resize(d.Count, 3).Value = v Set d = Nothing End Sub
-
-
Re: Macro to allow location and change file type to XLS
As for saving as xls .. Just replace 52 with 56
As for the first point, I have no idea what you are talking about .. Do you need dialog box to determine the path or what? -
Re: Macro to allow location and change file type to XLS
Hello
Try this codeCode
Display MoreSub ExportSheets() Dim wb As Workbook Dim ws As Worksheet ThisWorkbook.Sheets(Array("Output 1", "Output 2")).Copy ActiveWorkbook.SaveAs ThisWorkbook.Path & "\AnyName.xlsm", 52 Set wb = ActiveWorkbook For Each ws In wb.Worksheets ws.UsedRange.Cells.Value = ws.UsedRange.Cells.Value Next ws wb.Save End Sub
-
Re: Filter and Delete Rows
May be (because array begins with Zero Index)
Code
Display MoreSub DeleteRows() Dim x, y, z, i As Long, ii y = Array("Sarah", "David", "Tom", "Bethany", "John", "Katie") z = Array(0, 1, -1, 2, -2, 3) With ActiveSheet.UsedRange x = .Value For i = 2 To UBound(x, 1) If IsNumeric(Application.Match(x(i, 1), y, 0)) Then ii = Application.Match(x(i, 1), y, 0) - 1 If x(i, 5) <> z(ii) Then x(i, 1) = "" Next i .Value = x .Columns(1).SpecialCells(4).EntireRow.Delete End With End Sub
-
Re: Filter and Delete Rows
Corss-Post at this link too
http://www.vbaexpress.com/forum/showthre…and-Delete-Rows -