Posts by michaelnicolas

Re: Conciliation by reference and Amount

Hi miulrg,

The columns in the attached workbook change or we assume that will remain the same?

Re: Index Match and Vlookup too Slow - Please HELP

Hi geeasa,

Have a look at the "Scripting Dictionary" object that could replace your lookup formulas, ozgrid is a great place to do some research.

Excel samurai Jindon posts a lot of excellent solutions on this object.

I would go with this approach but if it's too complicated for you consider using the last row method for speeding up your existing routine, e.g in the match formula do not scan the whole of column "B" but rather find the last row in your file, so something like MATCH(C2, ' " & zy & " '!B1:B " & Lastrow, 0).

Hope that helps,
Nicolas

Re: Time interval formula issue

Hi,When you delete a cell then you formula causes a division by zero error (your COUNTIF part):Before any deletion =SUMPRODUCT({FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}/{1;2;2;2;2;1;1})+1After deletion =SUMPRODUCT({TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE}/{1;1;0;2;2;1;1})+1When a cell is missing what is the desired output?Best RegardsNicolas

Re: Find text if found return cell address

Try:
=ADDRESS(MATCH("*Hello*",A1:A20,0),1,4,1)

Re: VLOOKUP with changing number of columns

Hi mr444,

Test the macro below.
To test, delete the VLOOKUP formulas in your worksheet and run GetDailyCodes.
Your actual workbook's structure should be identical to the one you supplied.

Hope that helps.
Best Regards

Sub GetDailyQuotes()


Dim Dict As Object
Dim wsSource As Worksheet, wsTarget As Worksheet
Dim ValuesToFind As Variant
Dim RngSource As Range, RngTarget As Range
Dim LastRow As Long, LastColumn As Long, ArrayCounter As Long, DictCounter As Long, RowIndex As Long


Set wsSource = Worksheets("Sheet1")
Set wsTarget = Worksheets("Sheet3")


With wsTarget
    LastColumn = .Range("B1").End(xlToRight).Column
    ValuesToFind = WorksheetFunction.Transpose(.Range(.Cells(1, 2), .Cells(1, LastColumn)).Value)
End With


For ArrayCounter = LBound(ValuesToFind) To UBound(ValuesToFind)
    Set Dict = CreateObject("Scripting.Dictionary")
    With wsSource
        Set RngSource = .Rows(4).Find(What:=ValuesToFind(ArrayCounter, 1), After:=.Cells(4, 1), LookIn:=xlValues, LookAt:=xlWhole)
    End With
    If Not RngSource Is Nothing Then
        LastRow = RngSource.End(xlDown).Row
        For RowIndex = 1 To (LastRow - 4)
           Dict.Add RngSource.Offset(RowIndex, 0).Value, RngSource.Offset(RowIndex, 1).Value
        Next RowIndex
        With wsTarget
            LastRow = .Range("A" & Rows.Count).End(xlUp).Row
            Set RngTarget = .Rows(1).Find(What:=ValuesToFind(ArrayCounter, 1), After:=.Cells(1, 1), LookIn:=xlValues, LookAt:=xlWhole)
            If Not RngTarget Is Nothing Then
                For DictCounter = 2 To LastRow
                    If Dict.Exists(.Range("A" & DictCounter).Value) Then
                        RngTarget.Offset(DictCounter - 1, 0).Value = Dict.Item(.Range("A" & DictCounter).Value)
                    End If
                Next DictCounter
            End If
        End With
    End If
    Dict.RemoveAll
    Set Dict = Nothing
Next ArrayCounter


End Sub

Re: Save/retain leading zeros in Zip Codes in CSV file.

After you finish with the formula, have you tried copy and paste special values?

Re: Covert SQL export text string to Excel date "dd/mm/yyyy"

Hi Peskie,

Please see the attached solution using the DATE function.

Best Regards

Re: Copy table values to Array variables and use them in another sheet!

Please see attached. If you experience problems to adjust the macro in your actual workbook structure post a sample workbook with some dummy data but with the same structure.

Best Regards

Re: Tick pivot option "Defer Layout Update" with VBA

What Excel version are you using, and what exactly you do in:

Quote

I change the links of the pivots

?

Re: Tick pivot option "Defer Layout Update" with VBA

Hi Takis, welcome to Ozgrid.
Why you need to tick "Defer Layout Update" and then refresh?

Re: Link Formula to Sheet Index and not Sheet Name

Welcome to Ozgrid!
Try:

ActiveCell.FormulaR1C1 = "=" & Worksheets(1).Name & "!RC[25]"

Best Regards

Re: VBA Pivot Tables that can be used across all versions

Hi, I "cooked" up this but I'm not sure if its OK. It runs OK in 2007 but cannot test in other versions.

Public Function PivotInstance() As Integer


Dim x As Integer


x = Val(Application.Version)


Select Case x
    Case Is < 9
        PivotInstance = -1
    Case Is = 9
        PivotInstance = 0
    Case 10 To 11
        PivotInstance = 2
    Case Is = 12
        PivotInstance = 3
    Case Is = 14
        PivotInstance = 4
End Select
    
End Function

Then you use this function when you create the pivot table, e.g.

ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "Sheet1!R3C3:R11C5", Version:=PivotInstance()).CreatePivotTable _
        TableDestination:="Sheet1!R4C11", TableName:="PivotTable1", DefaultVersion _
        :=PivotInstance()
    Sheets("Sheet1").Select
    Cells(4, 11).Select

Hope it helps.
Best Regards

Re: Filtering pivottable using VBA

try

Sub test()


Dim ws As Worksheet
Dim pt As PivotTable


Set ws = Worksheets("Sheet1")  'Change
Set pt = ws.PivotTables("PivotTable1") 'Change


With pt.PivotFields("[Enter Field Name Here]")
    .PivotItems("1").Visible = False
    .PivotItems("3").Visible = False
End With


End Sub

Best Regards

Re: Dynamic array in VBA of newly added worksheets

Just adding to Smallman's suggestion you could:-
(a) add a prefix to your worksheets e.g. Sys_Example1, Sys_Example2.......(Sys for system)
(b) the worksheet opens then based on the user response, show the worksheets relevant to them (nothing changes to your current workbook)
(c) then the process suggested by Smallman will run and pick all the worksheets that are user created, i.e. not starting with the prefix Sys_.

Just a thought.....
Best Regards

Re: Split only numbers to next column

MID(text, start from character no, number of characters to return), so:
text = in cell A1
start from character no = start from 2 (avoiding the starting parenthesis)
number of characters to return = here it could be a fixed number, e.g. 11 (if the number length is always 11) or variable (which is the case because I didn't knew if the length is standard).

I used the SEARCH to find where the closing parenthesis starts - 2 (to avoid the space and the closing parenthesis)
SEARCH(find text, within text; start from character no)

Hope this helps.

Re: Split only numbers to next column

try =MID(A1,2,SEARCH(")",A1)-2)

Best Regards

Re: Multiple IF logic

Welcome to Ozgrid! Try =IF(B2<=30,"0-30",IF(AND(B2>30,B2<=60),"31-60",IF(AND(B2>60,B2<=90),"61-90",IF(AND(B2>90,B2<=120),"91-120","121+"))))

Best Regards

Re: using an if formula to give 7 results between seven seperate boundaries

Perhaps something like this (using Boolean logic)?
=((A1/B1)<1)*1+(AND((A1/B1)>=1,(A1/B1)<10))*2+(AND((A1/B1)>=10,(A1/B1)<15))*3+(AND((A1/B1)>=15,(A1/B1)<20))*4+(AND((A1/B1)>=20,(A1/B1)<25))*5+(AND((A1/B1)>=25,(A1/B1)<30))*6+(AND((A1/B1)>=30,(A1/B1)<35))*7

Best Regards

Re: Dynamic SQL Query; selected table based on cell value

Hi blab,

Test the attached.

Sub test()




Dim sql As String
Dim DailyDate As String




DailyDate = Format$(Sheet1.Range("A1").Value, "0#######")             'If the format of the date is like ddmmyyy, i.e. 05092012
                                                                                                'OR DailyDate = Sheet1.Range("A1").Value if the format of the date is dmmyyyy, i.e 5092012
sql = "SELECT DATE, SALE, QTY " & _
      "FROM P.dbo.Overview_" & DailyDate




End Sub

Best Regards

Re: Sumifs Formula in VBA using &lt;= and a cell reference

The line of code you posted has a typing error, it throws a Run-time Error 1004 "Application-defined or object-defined error".
When I changed from $B2$:$B$ to $B$2:$B$, it worked.
I will help more if you could upload a small workbook with dummy data but identical structure plus your code.