Posts by Timbo

Timbo · Mar 28th 2008

I have two sums as follows -

=VLOOKUP(B$4,PROFILE!$G$9:$I$557,3,FALSE)

=VLOOKUP(C$4,PROFILE!$G$9:$I$557,3,FALSE)-SUM(B$38:B38)

Sometimes the area they are looking up has no figures in it at all and if that is the case I want the sums to return a zero. Could someone help me achieve that please.

Thanks

Timbo · Dec 12th 2007

Re: Multiple Criteria Sum With Dynamic Ranges

Thanks Richard & Kris,

What you have given me works for one column, what about if I want to copy the calculation across 100 columns?

This defined data won't work because it doesn't refer to Column L and I can't set up 100 defined ranges.

Quote

kCol =DATA_STORE2!$K$4:INDEX(DATA_STORE2!$K:$K,LastRow)

If there a way of changing the calculation so that kcol will refer to lCol when copied one row to the right?

Timbo · Dec 11th 2007

Re: Use Indirect With A Named Range

Hi Kris,

Good to hear from you again, I have missed your expertise on the forum.

Tim.[hr]*[/hr] Auto Merged Post;[dl]*[/dl]Hi Kris,

Good to hear from you again, I have missed your expertise on the forum.

Unfortunately it doesn't work, it just returns 0.

The full sum is this - {=SUM(IF(DATA_STORE2!$I$4:$I$500=$A4,IF(DATA_STORE2!$J$4:$J$500="I", IF(DATA_STORE2!$DH$4:$DH$500>99,DATA_STORE2!$K$4:$K$500))))/$CZ4}

You may remember it you helped me with it.

500 is currently last row of the worksheet but the sheet keeps having data added to it. and I have to keep changing the 500 to say 510 then 520 etc.

The first worksheet in the workbook called DAILY_INPUT has this sum in it =COUNTA(A4:A505)+3 to count the number of rows with data in.

I thought that if I could use indirect to pick up this number the worksheet would always pick up all of the data on the worksheet with no changes required.

Tim.

Timbo · Dec 11th 2007

I have a sum part of which is $I$4:$I$500.

The 500 part of the sum is constantly being manually changed using edit replace because of a growing amount of data.

In cell A1 ona another worksheet within the workbook I use the Count function to count the number of rows containing data. This cell is named DataCols

Is it possible to combine $I$ with the named Range DataCols?

I have got this far - ="$I$"&INDIRECT("ColNo") which returns 500 if I remove "&I$"& in front of it.

Thanks

Timbo · Oct 11th 2007

Re: Combine If(isna With Sumproduct

Many Thanks.

Timbo · Oct 11th 2007

The following calculation works fine in every column but this one (COLUMN J).

=SUMPRODUCT(($A$7:$A$66="Media - INSERT")*($C$7:$C$66="Y")*(J$7:J$66))

Column J picks up data from the main frame and either returns a number or #N/A.

As a consequence I have a mix of numbers and #N/A's in the column and the sumproduct is just returning #N/A.

Can anyone suggest a way to overcome this please?

I have tried IF(ISNA(SUMPRODUCT( but I am not sure where to put the ,"" part of the calculation or whether there ought to be an extra part to the calculation i.e. *(J$7:J$66<>#N/A)

Many thanks.

Timbo · Sep 1st 2007

Re: Run-Time Error Setting Worksheet Variable

Sorry for the delay in getting back to all of you but a bit of a crisis situation here meant that I had to leave the project and come back to it.

Dave I am sorry I seem to offend you with my posts.

rbrhodes thanks for your macro it worked and highlighted the error.

Will your theory was right, the name picked up from the Membership Book is Blake's and the Worksheet is called BLAKES no apostrophe.

Is there a way that I can rectify that without changing the Sheet Name, after I get to this point?

Code

strWks = .Offset(0, 9).Value

I have tried -

Code

If strWks = "BLAKE'S" Then
Wks2 = "BLAKES"
End If

but it doesn't seem to work.

The sheets are linked through into Word using Mail Merge and to change the Sheet Name and redo all of that would take hours.

Timbo · Aug 29th 2007

Re: Macro Falls Over Just Before The End

Yes it is the word "Blakes" the name of one of the worksheets.

Timbo · Aug 29th 2007

This macro is falling over at the last hurdle and I just can't work out what is wrong.

The user is asked for one piece of information "Enter the ID Number.

What the macro should do then is go to Wks1 find the ID Number and change some cells as a result. This bit works.

It works fine until it reaches this point

Code

Set Wks2 = Worksheets(strWks)

7 rows from the bottom.

My intention was to capture the the name of another worksheet which is held on the same row as the ID Number on Wks1 and call it strWks.

Then further down the macro set the value of Wks2 to that of strWks so that the macro will then go to that sheet and remove data from the row with the same ID Number.

I get a Time Run Error 9.

It just seems to be the bit at the bottom where I am trying to identify Wks2 using strWks.

All help much appreciated.

Code

Sub Macro01C_Auto_Resign()
    Dim Wks1 As Worksheet, Wks2 As Worksheet
    Dim strFind As String, rngFound As Range
    Dim lngRow As Long, rngUnion As Range, strWks As String
     
    strFind = InputBox("Enter the ID Number")
    Set Wks1 = Worksheets("Membership Book")
    With Wks1
        Set rngFound = .Range("A1:A1000").Find(strFind, LookIn:=xlValues)
        If Not rngFound Is Nothing Then
            With rngFound
                strWks = .Offset(0, 9).Value
                lngRow = rngFound.Row
                .Offset(0, 13).Value = Format(Date, "dd/mm/yy")
                .Offset(0, 16).Value = "No"
            End With
            Set rngUnion = Application.Union(.Range(.Cells(lngRow, "R"), .Cells(lngRow, "S")), _
            .Range(.Cells(lngRow, "U"), .Cells(lngRow, "V")), _
            .Range(.Cells(lngRow, "X"), .Cells(lngRow, "Y")), _
            .Range(.Cells(lngRow, "AA"), .Cells(lngRow, "AB")), _
            .Range(.Cells(lngRow, "AD"), .Cells(lngRow, "AE")))
            rngUnion.ClearContents
        Else
            MsgBox "Value Not Found"
            Exit Sub
        End If
        Set rngFound = Nothing
        Set Wks2 = Worksheets(strWks)
        Set rngUnion = Nothing
    End With
     


    Wks2.Range("C1:C1000").Replace What:=strFind, Replacement:="", LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=False
     
    Set Wks1 = Nothing
    Set Wks2 = Nothing
     
End Sub

Display More

Timbo · Jul 16th 2007

Re: Userform Basics

Hi Roy,

I had limited time last week but I have taken one of the examples on the page that Kris suggested last week and I have worked out how to add more boxes for data to be typed into and how to get the tab order right.

I want the data that goes into these boxes to direct a new macro (not written yet) to certain directories, open specific files and rename specific them.

So my next step will be to work out how to link the Userform to the macro. One of the pieces of data I am capturing is a filename CFINS1 an example of how a macro captures that one piece of information from the Userform and open the file would probably get me started on the next stage.

Many thanks.

Timbo · Jul 13th 2007

I have been looking into refining the way a couple of big macros that I wrote several years ago work and have decided that instead of loads of inputboxes that a Userform might be the way to go.

I have never used them, don't know how to link code to the objects to make them work and can on make a guess as to the right way to start going about this.

Can anyone suggest a sort of Userforms for Dummies site where I could pick up the basics?

I am going looking toward having a box come up where the user can enter about 8 pieces of data and based on that data the macro will then go off and open specific files anc change specific data.

Any help appreciated.

Timbo · Jul 4th 2007

Re: Change Code To Activeworkbook

Thanks Will, just what I needed!

Timbo · Jul 4th 2007

This piece of code successfully replaces a module in a range of workbooks -

Code

Sub CopyOneModule01()
    Const strModName As String = "Module1"
    Dim FName As String
    Dim vWbkNames As Variant
    Dim lLoop As Long
    
    vWbkNames = Array("CFADS01", "CFADS02", "CFADS03", "CFADS04")    
    
    With Workbooks("CF266") 'copies from here
        FName = .Path & "\code.txt"
        .VBProject.VBComponents(strModName).Export FName
    End With
     
    For lLoop = LBound(vWbkNames) To UBound(vWbkNames)
        With Workbooks(vWbkNames(lLoop)).VBProject.VBComponents
            On Error Resume Next
            .Remove VBComponent:=.Item(strModName)
            On Error GoTo 0
            .Import FName
         End With
    Next lLoop
    
End Sub

Display More

Is there a way to change the code so that I can slot it into another sub-routine which I use to open a large range of workbooks and make specific changes so that the sub routine works on the Activeworkbook?

ie. vWbkNames = ActiveWorkbook.Name

I have tried this but the For lLoop code further down the macro doesn't like it. I asume because there isn't an array therefore the loop isn't needed.

I have commented out the For lLoop piece of code and the macro runs but the module isn't copied across.

Timbo · Jul 2nd 2007

Re: Remove Vba From Worksheet

Thanks Andy, much appreciated.

Timbo · Jul 2nd 2007

Re: Remove Vba From Worksheet

Hi Andy.

I have changed Thisworkbook to Activeworkbook and this works fine does it look okay?

Code

Option Explicit


Sub aDeleteSheetEventCode()
     ''Needs Reference Set To "Microsoft Visual Basic For Applications Extensibility"
     'Tools>References.
    
    Dim strCodeName As String
    
    strCodeName = ActiveWorkbook.Worksheets("CAMOB").CodeName
    
    With ActiveWorkbook.VBProject.VBComponents(strCodeName).CodeModule
        .DeleteLines 1, .CountOfLines
    End With


    
    strCodeName = ActiveWorkbook.Worksheets("CAMLE").CodeName
    
    With ActiveWorkbook.VBProject.VBComponents(strCodeName).CodeModule
        .DeleteLines 1, .CountOfLines
    End With
End Sub

Display More

Timbo · Jul 2nd 2007

Re: Remove Vba From Worksheet

Hi Andy,

In break mode the Macro runs down to the last line and then I get a message "Can't enter break mode at this time. With options Continue End Debug or Help.

Continue lets the macro finish and removes the code from the worksheet.

If I run the macro with no code in the worksheet it doesn't stop all.

Is there anyway to overcome this and can I run it from personal.xls instead of from the workbook with the code in it?

Timbo · Jul 2nd 2007

Re: Remove Vba From Worksheet

Hi Andy,

I have tried this

Code

Set objVBCode = objVBProject.VBComponents("Sheet1").CodeModule

Is it because Sheet1 (CAMOB) is a worksheet and not a CodeModule? Can In replace CodeModule with something that will pick up the code in the worksheet?

Timbo · Jul 2nd 2007

Re: Remove Vba From Worksheet

Where do I put the quotes to make this work? do I split the sheet and the worksheet name like this or is there a way to combine them?

Code

Set objVBCode = objVBProject.VBComponents("Sheet1")("CAMOB").CodeModule

Timbo · Jul 2nd 2007

Re: Remove Vba From Worksheet

Hi Andy,

Thanks for your help, your code has fixed that problem but now it stops further down the macro here -

Code

Set objVBCode = objVBProject.VBComponents("CAMOB").CodeModule

I get a run time error 424 Object required

Is it because the worksheet is called CAMOB but in the Project Window it appears as Sheet1 (CAMOB)?

Timbo · Jun 29th 2007

Re: Remove Vba From Worksheet

Hi Pike,

I get a Compile error: Syntax Error at this point -

Code

Set objVBProj  ThisWorkbook.VBProject

Posts by Timbo

Get Sum To Return A Zero

Multiple Criteria Sum With Dynamic Ranges

Multiple Criteria Sum With Dynamic Ranges

Multiple Criteria Sum With Dynamic Ranges

Ignore Error Cells In Multiple Condition Sum

Ignore Error Cells In Multiple Condition Sum

Run-Time Error Setting Worksheet Variable

Run-Time Error Setting Worksheet Variable

Run-Time Error Setting Worksheet Variable

Userform Basics

Userform Basics

Change Code To Activeworkbook

Change Code To Activeworkbook

Remove Vba From Worksheet

Remove Vba From Worksheet

Remove Vba From Worksheet

Remove Vba From Worksheet

Remove Vba From Worksheet

Remove Vba From Worksheet

Remove Vba From Worksheet