Posts by Craig Ottley

Re: Copying an unknown range of cells from an unknown number of excel files to one ta

Brandonien please use Code tags when using VBA code.

You can assign a string with a folder location instead of using a Default File Path...

IE

Dim strFolderPath As String
strFolderPath = "C:\Users\Gnomish Fire\Desktop\RX-8 Tuning\Current Tune\"

Re: Calling Macros in an if statement

You don't really need a macro to do this for you.... You can use a formula with an address formula??

Try the following.

=IF(Comments!B32=0, HYPERLINK("[NC Table2.2.xls]Comments!"&ADDRESS(ROW(),2,1)&"","Create"), HYPERLINK("[NC Table2.2.xls]Comments!"&ADDRESS(ROW(),2,1)&"","View"))

HTH

Craig

Re: Excel adding an unwanted extra dimension to my array when a range is assigned to

It's probably something to do with you declaring the array as a variant... Even if it is a one dimensional array a variant seems to want to know this.

For a more "correct" style of programming you could declare the array as say type String and then fill the array through a loop....

See example below.

Option Explicit
Sub FillArray()


Dim wksArrayValues As Worksheet
Dim strArrayValues(1 To 19) As String
Dim rngCell As Range
Dim intIndex As Integer


    Set wksArrayValues = Sheet1
    intIndex = 1
    
    For Each rngCell In wksArrayValues.Range("B6:B24")
        
        strArrayValues(intIndex) = rngCell.Value
        
        intIndex = intIndex + 1
    Next rngCell
    
End Sub

HTH

Re: extract path from a cell with a hyperlink formula

You should be able to get access to the Formula and then use some String functions to get the parts you need....

See example

Option Explicit
Sub GetHyperlinkFromRange()


Dim rngCell As Range
Dim strHyperlink As String


    Set rngCell = Sheet1.Range("C2")
    
    If rngCell.HasFormula Then
        If Mid(rngCell.Formula, 2, 9) = "HYPERLINK" Then
            strHyperlink = Mid(rngCell.Formula, 13, Len(rngCell.Formula))
            strHyperlink = Left(strHyperlink, Application.WorksheetFunction.Find(",", strHyperlink) - 2)
            Debug.Print strHyperlink
        End If
    End If
    
End Sub

HTH

Craig

Re: VB Applications?

Hi Riverstyx,

You normally won't find "Free" applications. People generally spend a lot of time developing applications in Access, Excel, Visual Studio etc... Saying that you check check out the Open source code thread on this site http://www.ozgrid.com/forum/forumdisplay.php?f=13. For none Access and Excel look on Codeplex

HTH

Craig

Re: Excel adding an unwanted extra dimension to my array when a range is assigned to

Hi OpiumTea,

Welcome to OzGrid. There are a few ways to tackle this, the first is making sure all your other procedures can handle multi dimensional arrays, but I don't know how many procedures you'd need to change. Another is to set-up a helper function that returns the variant as a one dimensional array...

Try the following.

Dim structures_listTemp() As Variant
Dim structures_list() As Variant
ReDim structures_listTemp(1 To member_count)


structures_listTemp = Worksheets(families_sheet).Range(structures_list_location).Value
structures_list = GetSingleArray(structures_listTemp)


Private Function GetSingleArray(ByRef vntMemberList As Variant) As Variant


Dim vntSingleArray() As Variant
Dim intIndex As Integer


ReDim vntSingleArray(1 To UBound(vntMemberList, 1))


For intIndex = 1 To UBound(vntMemberList, 1)
    vntSingleArray(intIndex) = vntMemberList(intIndex, 1)
Next intIndex


GetSingleArray = vntSingleArray


End Function

HTH

Craig

Re: Copying an unknown range of cells from an unknown number of excel files to one ta

Try the following attachment... This should be more than a head start for you.

forum.ozgrid.com/index.php?attachment/38609/

HTH

Craig

Re: Excel 2007: Drop down list selection cleared when reopen workbook

Simple answer is de-merge the cells and try again...

Re: Filtering Pivot Table In VBA

Hi Matt,

Have you got a sample workbook with the pivot table in it? Controlling a pivot table programmatically can be a pain at times.

Re: coordinates; array formulae and using results to fuel further array formulae.

Hi Danny,

From what you've provided I've come up with a solution that uses helper columns...

You should be able to get your head around the attached.

forum.ozgrid.com/index.php?attachment/38594/

HTH

Craig

Re: RTD Links not working in Excel 2010

Sounds like something isn't registered properlly. See the following link helps...

How to set up and use the RTD function in Excel

Quote

The RTD COM automation add-in must be created and registered on a local computer. If you haven't installed a real-time data server, you will get an error message in a cell when you try to use the RTD function.

Re: Copying an unknown range of cells from an unknown number of excel files to one ta

Hi Brandonien,

Welcome to Ozgrid. This question comes up quite a lot on this forum. Have you tried searching the forum? There are plenty of examples that you should be able to adapt...

Craig

Re: Cannot open PivotTable source File

Have you tired using UNC Links?

Re: Populating combobox in different sheets

Hi Carlo,

Have you actually got a problem you need solving or are you just sharing code?

Re: Repairing, recovering, or viewing corrupted "drawing.xml" files in Excel 2007?

Try opening it in Open Office....

Re: Match two columns and sum the corresponding value from the third column

The codes look different to me... If you match the codes I don't see why DSUM wouldn't work for you.

Re: How to pass Values from Text boxes to SQL Query when a macros is called by a butt

Try the following...

sqlstr = "SELECT * FROM table name where ID = '" & TextBox1.Text & "' & and name = '" & TextBox2.Text & "'"

Re: delete records use aa very very lot time

A & B table are a SQL Alias....

PKEY is the join on the tables for the primary key column

Re: VBA Copy Paste Loop Question

Hi Pink705,

Welcome to OzGrid. Do you want to concatenate the rows into one cell? If you look at the possible answers at the bottom of this thread you should be able to see loads of examples. Otherwise post a sample workbook with your current and desired result.

Thanks

Re: Excel / Access update of chart

Sounds completely plausible to me.... You can use the offset functions in the charts to allow for dynamic ranges when the sheet is updated.