Posts by Craig Ottley

Re: Making a variable available amongst userform routines

Hi Xhunmas,

Welcome to Ozgrid! Personally I'd approach this at a different angle... Save having Code in 12 separate workbooks I'd look into creating an Add-In

HTH

C

Re: Macro to copy and paste data between date&time code

Hi Matt,

Welcome to OzGrid. Have you got a sample workbook? Seems simple enough just creating a loop and array....

C

Re: Problems with values

Hi Arran,

Looks like you need to add some IF formula.... Try putting this in cell A23 =IF(TRIM('New Order'!E26)="","",'New Order'!E26)

HTH

C

Re: Quintile Distribution and Lorenz Curve

Hi Mike,

Welcome to OzGrid! Have you tried searching the forum for your answer? Try this link.... If you can't find anything post a sample workbook so we'll be able to take a look at it for you

C

Re: Show Security Dialog Box, not Ribbon, on Open

Hi mlmaxey,

Welcome to Ozgrid! There's a trick you can do by making your project file an Add-In! Once you've saved the project file as an add-in the users won't even require a dialogue box to appear....

HTH

C

Re: Nested Group By Query

Hi dclark,

What RDBMS are you using?

Re: Move shape below last entry

What have you tried so far? do you have a sample workbook?

Re: Timing solution and sorting of data

Hi Qayyum,

Sorry for the delay, I'm currently on holiday and just been trying to clear a backlog of emails. I'll be able to take a look at this on Tuesday for you.

Craig

Re: Vba excel clean

Hi billiamb,

Welcome to OzGrid. Your question is a bit vague... Are you able to explain a bit more what you need doing to your string?

Thanks

Craig

Re: MS QUERY error retrieving data - ODBC Text Driver Login Failed

You can create a pivot chart using VBA. The best way for Pivots is to actually record a macro and then alter from there...

HTH

Re: Excel to Update all records to SQL Database

Excellent, that's the answer I was hoping for!

The way I'd do this is to load all of the data back into a temporary/staging table in SQL Server and then perform an update statement doing a join on the tables. Does this make sense to you so far?

Craig

Re: Timing solution and sorting of data

Hi Qayyum,

Post a sample workbook on here and we'll be able to help...

Craig

Re: MS QUERY error retrieving data - ODBC Text Driver Login Failed

Hi Mark,

Are you able to load any of the source files into a access/sql server database?

Re: MS QUERY error retrieving data - ODBC Text Driver Login Failed

Hi Mark,

Are you able to access the database directly via a connection string? It's far easier to go directly to the database and run your queries there. Failing that are you able to load the text files into your own database somewhere?

Craig

Re: Excel to Update all records to SQL Database

Hi Paul,

What kind of access do you have on the Database Server? Depending on what kind of control you have, I can pick the appropriate solution.

Craig

Re: Move shape below last entry

Hi errtu,

Hopefully this should solve your problem...

Dim wks As Worksheet
Dim lngLastRow As Long
Dim myShape As Shape
    
    Set wks = Sheet1 ' Sheet code name here
    lngLastRow = wks.Cells(Rows.Count, 1).End(xlUp).Row + 1
    
    Set myShape = wks.Shapes("ReceiptFoot")
    
    myShape.Top = wks.Cells(lngLastRow, 1).Top
    myShape.Left = wks.Cells(lngLastRow, 1).Left

Re: Timing solution and sorting of data

Hi Qayyum,

Welcome to OzGird. Have you tried recoding a macro to see if that can assist you? You'll probably only need to alter the macro slightly for dynamic ranges etc but it should be a good starting point. If you get stuck post a sample workbook and we'll be able to point you in the right direction

Craig

Re: Help hiding columns that have checkboxes associated please

Hi Linda,

Welcome to OzGrid. Have you tried creating a custom views to see if this solves your problem?

Craig

Re: Excel to Update all records to SQL Database

Hi Paul,

Welcome to OzGrid. I've moved your thread to the correct forum in this instance and placed code tags around your code.

Anywho, a couple of things I need to know. When you're pulling the data into excel are you expecting users to update some of the values apart from the Issue_No? How many rows of data are you expecting to update each time you run this procedure? IE 10 rows, 1,000, 10,000 rows etc...

Craig

Re: Select Cell Based on Value from Formula

Hi Chineth,

I'm slightly confused as to why you're struggling on this... If you've managed to use VLOOKUP to find the family name it looks as though you've already answered your own question? If not please elaborate.

Craig