# Posts by michaelnicolas

• ## Conciliation by reference and Amount

Re: Conciliation by reference and Amount

Hi miulrg,

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

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

• ## Time interval formula issue

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

• ## Find text if found return cell address

Re: Find text if found return cell address

Try:

• ## VLOOKUP with changing number of columns

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

• ## Save/retain leading zeros in Zip Codes in CSV file.

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?

• ## Covert SQL export text string to Excel date "dd/mm/yyyy"

Re: Covert SQL export text string to Excel date &quot;dd/mm/yyyy&quot;

Hi Peskie,

Please see the attached solution using the DATE function.

Best Regards

• ## Copy table values to Array variables and use them in another sheet!

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

• ## Tick pivot option "Defer Layout Update" with VBA

Re: Tick pivot option &quot;Defer Layout Update&quot; with VBA

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

Quote

I change the links of the pivots

?

• ## Tick pivot option "Defer Layout Update" with VBA

Re: Tick pivot option &quot;Defer Layout Update&quot; with VBA

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

• ## Link Formula to Sheet Index and not Sheet Name

Re: Link Formula to Sheet Index and not Sheet Name

Welcome to Ozgrid!
Try:

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

Best Regards

• ## VBA Pivot Tables that can be used across all versions

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.

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

Code
``````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

• ## Filtering pivottable using VBA

Re: Filtering pivottable using VBA

try

Best Regards

• ## Dynamic array in VBA of newly added worksheets

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

• ## Split only numbers to next column

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.

• ## Split only numbers to next column

Re: Split only numbers to next column

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

Best Regards

• ## Multiple IF logic

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

• ## using an if formula to give 7 results between seven seperate boundaries

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

• ## Dynamic SQL Query; selected table based on cell value

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

Hi blab,

Test the attached.

Best Regards

• ## Sumifs Formula in VBA using <= and a cell reference

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.