• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Search results

  1. J

    understanding Loops with nested Arrays.

    Try Sub test() Dim a, b, i As Long, ii As Long, iii As Long, n As Long With Sheets("sheet1") With .Range("a5", .Range("a" & Rows.Count).End(xlUp)).Resize(, .Cells.SpecialCells(11).Column) a = .Value: n = 3 b = .Resize(.Rows.Count * .Columns.Count).Value...
  2. J

    How to copy fixed values into the blank cells?

    Try Sub test() With Range("a1").CurrentRegion '★ alter to suite. With .Offset(1, 1).Resize(.Rows.Count - 1, .Columns.Count - 1) On Error Resume Next .SpecialCells(4).Formula = "=r" & .Row - 1 & "c" On Error GoTo 0 .Value = .Value...
  3. J

    Incrementing values

    Insert following code to each userform Private Sub CommandButton2_Click() If Me.ComboBox1.ListIndex > -1 Then Sheets("budget").Cells(Me.ComboBox1.ListIndex + 2, Application.Match(Split(Me.Caption)(1) & _ "*", Sheets("budget").Rows(1), 0)).Value = Me.TextBox1 End If...
  4. J

    remove duplicate characters in one cell

    UDF Use in cell like =UniqLast4(Sheet1!A2) Function UniqLast4(ByVal txt As String) With CreateObject("VBScript.RegExp") .Pattern = "(.*)(\d)(.*)(\2)(.*)" Do While (.test(txt)) * (Len(txt) > 4) txt = .Replace(txt, "$1$3$4$5") Loop UniqLast4 =...
  5. J

    Creating Automatic Price Quote With Userform That Contains Cascading Drop-Down Lists

    See if this is how you wanted. Option Explicit Private dic As Object Private Sub UserForm_Initialize() Dim a, i As Long Set dic = CreateObject("Scripting.Dictionary") dic.CompareMode = 1 a = Sheets("database").Cells(1).CurrentRegion.Value For i = 2 To UBound(a, 1)...
  6. J

    VBA-Excel Programming sub code!

    It will be just wasting time without real data.
  7. J

    Hiding columns

    Is that Sub unhide() Rows.Hidden = False End Sub ?
  8. J

    VBA-Excel Programming sub code!

    That means you deleted the function code "Function GetSortVal" that I have posted. And you might also delete VSortM sub procedure...
  9. J

    Hiding columns

    Messy data range... 1) Clear col.A or even delete col.A Sub test() Dim i As Long With [b3].CurrentRegion For i = 2 To .Rows.Count .Rows(i).EntireRow.Hidden = Application.CountIf(.Rows(i).Offset(, 1), "No Promotion") = .Columns.Count - 1 Next End With End Sub
  10. J

    VBA-Excel Programming sub code!

    My guess... If you upload a small sample workbook with before/after, it can be easily done. Sub test() Dim a, e, txt As String, i As Long Const KeyCol As String = "2,3,1,4,5" '<- columns to sort in order With Cells(1).CurrentRegion a = .Value ReDim Preserve a(1 To...
  11. J

    Macro to find diffrence b/w two sheets and copying the diffrence data into another sheet from oldfil

    One possibility that I can think is, like I mentioned in #7, any cell have an error value...
  12. J

    VBA-Excel Programming sub code!

    Johan1959 If you can not adopt my code to your range then just try change the "test" sub procedure like below while other procedures remain intact. Sub test() Dim a, i As Long Const KeyCol As Long = 1 Rem KeyCol is a relative column position within a range Rem If range starts...
  13. J

    need vba code that can rearrange records having a repeated id#

    1) You should respond to your previous thread before you start ask next question. http://forum.chandoo.org/threads/code-that-can-get-a-price-record-w-multiple-values-in-single-cell-replicated-into-several-unique.33849/ 2) Meaningless heading in "Raw data" sheet after col.H should be deleted...
  14. J

    Code that can get a price record w multiple values in single cell, replicated into several unique

    Expand the size of array b for the safty e.g to 100000 ReDim b(1 To 100000, 1 To UBound(a, 2))
  15. J

    Code that can get a price record w multiple values in single cell, replicated into several unique

    1) Place a button for yourself. 2) Change With Sheet(3).[a2] to your actual output sheet name Sub test() Dim a, b, e, s, i As Long, ii As Long, n As Long, x, y a = [a5].CurrentRegion.Value ReDim b(1 To 10000, 1 To UBound(a, 2)) For i = 2 To UBound(a, 1) If a(i, 8) = ""...
  16. J

    How to separate string and date value from a string.

    Sub test() Dim a, i As Long, myMonth As String, m As Object With Cells(1).CurrentRegion.Resize(, 5) .Offset(2, 1).ClearContents a = .Value With CreateObject("VBScript.RegExp") .Pattern = "(.+) ((\d{2})/(\d{2})/(\d{4})|(\d{2})([A-Z]{3})(\d{4})).*"...
  17. J

    VBA-Excel Programming sub code!

    Just test it so that you will see how it sorts the data. By the way, x is a an array consists of Numeric value and string value and it sorts as 1 2 6 7A 7A 7B 7B 18A 18b 18C 21 22 23 27A 27B 33 35
  18. J

    VBA-Excel Programming sub code!

    Johan1959, I wrote this some years back. See if this is kind of what you are trying to do. Sub test() Dim x, i As Long x = [{"7B";"7A";21;22;23;"18b";"18C";6; 2;1;"7B";"7A";"18A";33;35;"27A";"27B"}] With Cells(1).Resize(UBound(x), UBound(x, 2)) .Value = x MsgBox...
  19. J

    Identify first row of UsedRange

    You are welcome and thanks for the feedback.
  20. J

    Identify first row of UsedRange

    Correct. So it needs to be from the last cell in order to find A1, if it has data...
  21. J

    Identify first row of UsedRange

    OK, just run the code and see the difference Sub test() Cells.Clear Range("a1,c2,e4").Value = 1 Debug.Print "Row", "Column" Debug.Print Cells.Find("*").Row, Cells.Find("*").Column Debug.Print Cells.Find("*", Cells(Rows.Count, Columns.Count), , , 2, 1).Column, _...
  22. J

    Identify first row of UsedRange

    This should tell you what it is. With ActiveSheet MsgBox .Cells(.Rows.Count * .Columns.Count).Address End With Same as With ActiveSheet MsgBox .Cells(.Rows.Count, .Columns.Count).Address End With The very last cell within a worksheet.
  23. J

    Macro to find diffrence b/w two sheets and copying the diffrence data into another sheet from oldfil

    Update Sheet2... Sub bb() Dim a, i As Long, ii As Long, e, txt As String, w, dic As Object Set dic = CreateObject("Scripting.Dictionary") dic.CompareMode = 1 With Sheets("sheet2").Cells(1).CurrentRegion a = .Value .Resize(.Rows.Count -...
  24. J

    Macro to find diffrence b/w two sheets and copying the diffrence data into another sheet from oldfil

    OOps found one more typo... Sub bb() Dim a, i As Long, ii As Long, e, txt As String, w, dic As Object Set dic = CreateObject("Scripting.Dictionary") dic.CompareMode = 1 With Sheets("sheet5") a = .Range("a1").CurrentRegion.Resize(, .Cells.SpecialCells(11).Column).Value...
  25. J

    Macro to find diffrence b/w two sheets and copying the diffrence data into another sheet from oldfil

    Just in case if you are missing my last code in post #25 in previous page, I have corrected typo that was in my original code.
Back
Top