Posts by dcrose

Re: Use Cell Colour In Formula Without Vba

Hi Parsnip,

Thanks for your help - I haven't seen that before!

AAE - I agree changing the design of the sheet would be better solution, but it was just a general point that arose from a specific example - ie can you reference any cell format properties in a formula withoutusing vba?

Thanks again all.
Regards,
Dave

Hi,
This is just a quick "can it be done" question.

Is it possible to reference a cell's colour in a formula within a spreadsheet - without using VBA?

i.e If Cell A1 has a fill colour of yellow, can I have a formula in cell A2 that says
If Cell A1 fill colour = Yellow, result = 1 etc.

I know this can be done in VBA, and I have done that, but just wondered if possible this way.

Thanks & regards,
Dave

Hi,
I'm in the process of writing/editing a macro, but after I run the macro once, the Expressions in the Locals Watch window disappear, and if I try to run it again it produces an "Excel has encountered an error and needs to close".

The macro probably doesn't make much sense out-of-context, but perhaps there's a glaring error that's causing this ...
A guess - possibly related to the Error Handling in the vba? (1st time I've used this in a macro).

Any ideas/advice anyone?

Option Explicit


Sub Compare()


Dim strNaspK As String
Dim strCtryK As String
Dim intCtryRev As Integer
    
    Do Until ActiveCell.Offset(1, 0) = ""
    ActiveCell.Offset(1, 0).Select
    
    strNaspK = ActiveCell
    strCtryK = ActiveCell.Offset(0, 2)
    
    On Error GoTo ErrJump:
      
    Sheets("Comparison").Select
    Columns("G:G").Select
        Selection.Find(What:="" & strNaspK & "", After:=ActiveCell, LookIn:=xlFormulas, _
            LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
            MatchCase:=False, SearchFormat:=False).Activate
    ActiveCell.Select
    intCtryRev = ActiveCell.Offset(0, 18) ' no of rows of data for same NASP id
       
    ActiveCell.Range("A1:C" & intCtryRev).Select
        Selection.Find(What:="" & strCtryK & "", After:=ActiveCell, LookIn:=xlFormulas, _
            LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
            MatchCase:=False, SearchFormat:=False).Activate
    ActiveCell.Select
    ActiveCell.Offset(0, 15).Range("A1").Select
        Selection.Copy
        
        Sheets("Comparison2").Select
        ActiveCell.Offset(0, 18).Select
            ActiveSheet.Paste
        ActiveCell.Offset(0, -18).Select
Jumpout:
        Sheets("Comparison2").Select
    Loop


Exit Sub
ErrJump:
Resume Jumpout:
End Sub

Basically, I've got two sets of data on different sheets, with NASP ID and country being variables. Just trying to import the data from the 'Comparison' sheet, if both the NASP id and country matches that on the first sheet, established by the variables.

Thanks for any advice.
Regards,
Dave

Re: Two Sheets Same Data No Colour

Hi Kevin,
Presumably, Workbook B is just linked to Workbook A, in which case it will only bring through cell contents - not formats.

You could perhaps try a macro that operates on a change event - and then just copies Workbook A to Workbook B - either by cell, or sheet - depending on what you are trying to do.

It might help others to help you if you give a few more details about what you are trying to do, then the real Excel gurus on here will be able to help you out (I'm an amateur! )

Regards,
Dave

Re: "nested If" In Vba Code

Thanks Parsnip -

I didn't know you could combine an "If" and "Or" statement in VBA (presumably means you can do If...And as well). That will really help me a lot.

The Select Case often doesn't meet my needs as it's normally a long macro underneath this bit and I don't want to duplicate the code if I can help it. Also, I'm often trying to combine 2 different conditions.

If ...Or is great though - thanks again.
Regards,
Dave

Hi everyone,
I'm fine with Nested if formulas in Excel, but what do you do when a macro needs a nested if - or an "And" "Or" type argument?

e.g. in a Loop macro, I only wanted it to run if the Country code in col A was "UK" or "IE". This is how I got round it, but is there a more elegant solution - the below would get a bit messy if there were more than 2 variables involved.

If ActiveCell = "UK" Then
        GoTo JumpIn
      End If
    If ActiveCell <> "IE" Then
        GoTo Jumpout
      End If
Jumpin:
' rest of macro I want to run


Jumpout:

The above works, but hopefully there's a better way to do this.
Many thanks,
Dave

Re: Vba: Set Print Format Across Multiple Sheets

Hi Jon,

Many thanks for your help. I think that will work fine.

Regards,
Dave

Hi,
I'm trying to write a macro to select all the Sheets in a Workbook, and set some properties [Auto ColumnWidths, Landscape, and Fit to 1 page wide] for all of them.

I don't know the names of the sheets, nor how many there will be - this part is tagged on the end of a long macro that creates new files and pastes various data into them.

The code below only seems to work on the Active sheet - not any of the others selected. Curiously, I can set a specific column width for all sheets, but not Auto Widths.

With ActiveWorkbook
                Sheets.Select
            With ActiveSheet.PageSetup
                .PrintTitleRows = "$1:$1"
                .Orientation = xlLandscape
                .Zoom = False
                .FitToPagesWide = 1
                .FitToPagesTall = False
            End With
            End With
        
            Sheets.Select
            ActiveWindow.Zoom = 80 ' This line works!
                Cells.Select
                    Selection.RowHeight = 13.5
                    Selection.EntireColumn.AutoFit
                Columns("C:C").Select
                    Selection.ColumnWidth = 34 ' This line works
            Range("A1").Select

Many thanks if anyone can help ... been at this for hours now, and not getting any nearer. I wouldn't mind, but I've done all the complex stuff!!

Thanks & regards,
Dave

Re: Inverted Commas & Range References-vba

Thanks Roy & filo65, - that works grand ... I thought I needed a whole forest of speech marks, but obviously that's where I was going wrong!!

Regards,
Dave

Hi,
I'm trying to combine 2 variable column refs and 2 fixed numbers to select a range in VBA, but I can't get the correct syntax re the number of inverted commas.

Range("" & strFrom & "4"":" & strTo & "7""").Select

(strFrom and strTo are the column variables, already defined)

I've tried every combination (except the right one!) for over an hour and just cannot get this to work.

If someone can also explain the logic around the number/placement of inverted commas it would be much appreciated - since they're all straight, I'm never sure whether I'm closing a set or opening a new set.

Many thanks,
Dave

Re: Pivot tables: auto-update 1 when other is changed

Hi,

jetted - thanks for your post, but I think you may have misunderstood ... the base data for the pivot tables is not changing - it's just the viewing options for the pivot tables.

Carlmack - thanks for the code - I tried it but strangely it seemed to work once - the first time - and then wouldn't work again after that.

Unfortunately I think I'm a bit out of my depth here, but I wrote a better normal macro to match the pivot tables, so I'll just place a button next to Pivot Table 1 and users can activate it by that.

Many thanks for your efforts.
Best regards,
Dave

Hi,
I have 3 pivot tables on the same page - they all have the same PAGE and ROW fields, just different column and data fields. (may seem weird, but I'm producing reports for non-Excel people and this seems the only way to display info in user-friendly way).

Problem: If a user changes the selection in a Page field, I need the other 2 pivot tables to automatically select the same Page field, so the pivot tables are still all like-for-like.

I've started writing a macro which I can run to do this - below - but
1) I need it to run automatically when any of the page or row fields are updated
2) my macro is probably far too basic for my needs - one of the page fields has 245 values, so the below isn't really going to cut it!! ... any suggestions to improve this would be greatly appreciated.

Sub PivotMatch1()
    Select Case ActiveSheet.PivotTables("PivotTable1").PivotFields("Country Name").CurrentPage
        Case Is = "Ireland"
            ActiveSheet.PivotTables("PivotTable2").PivotFields("Country Name").CurrentPage = "Ireland"
            ActiveSheet.PivotTables("PivotTable3").PivotFields("Country Name").CurrentPage = "Ireland"
        Case Is = "All"
            ActiveSheet.PivotTables("PivotTable2").PivotFields("Country Name").CurrentPage = "All"
            ActiveSheet.PivotTables("PivotTable3").PivotFields("Country Name").CurrentPage = "All"
        Case Is = "United Kingdom"
            ActiveSheet.PivotTables("PivotTable2").PivotFields("Country Name").CurrentPage = "United Kingdom"
            ActiveSheet.PivotTables("PivotTable3").PivotFields("Country Name").CurrentPage = "United Kingdom"
    End Select
End Sub

I've done the below in the Sheet code to try and auto-run it, but it doesn't work.

Private Sub Auto()
Call PivotMatch1 
End Sub

Thanks for any help.
btw - can anyone recommend a good book so I can develop my macro skills?
Dave

Re: Using Variants in Macro Formulas

Thanks Norie ... have to watch out for that one!

Re: Using Variants in Macro Formulas

Hi Norie,
My problem was Run-time Error 1004
The full code I was trying to use was:

ActiveCell.Offset(-1, 0).Select
 strBottom = ActiveCell.Address
    
 Selection.End(xlUp).Select
 ActiveCell.Offset(1, 0).Select
 strTop = ActiveCell.Address
   
 Selection.End(xlDown).Select
 ActiveCell.Offset(1, 0).Select
    ActiveCell.FormulaR1C1 = "=SUBTOTAL(9," & strTop & ":" & strBottom & ")"

I have just found a way round it though - much simpler & makes the next 53 columns much easier!

y = ActiveCell.Row
 x = y - 11
 ActiveCell.FormulaR1C1 = "=SUBTOTAL(9,R[-" & x & "]C:R[-1]C)"

I'd still be interested what's wrong with the first one though as I keep on coming across this problem ...

Many thanks,
Dave

Hi,
I am trying to insert a simple subtotal formula in a macro, using variants as the bottom of the range will change.
What I can't work out is the placement of the ""s and the &s

I've already defined strTop as the top cell in the range and strBottom as the bottom cell (cell above the subtotal).

Currently I have -

ActiveCell.FormulaR1C1 = "=SUBTOTAL(9," & strTop & ":" & strBottom & ")"

I've tried this and a dozen+ variations but Excel won't accept them!!
Any help - and general advice on how to use the ""s and &s... in these situations - greatly appreciated.

Also, is there a simpler way to do this? I need to copy this formula across the next 53 columns, and the only way I can think of is to use the above and then redefine StrTop and StrBottom each time for the next 53 columns. :?

Many, many thanks for any help.
Dave

Re: Cannot Cut & Insert copied rows

Yep, that will be my problem then!

Thanks Will!

Hi,
This has got me foxed ... someone please help!!
When I try to cut a row and then Insert copied rows somewhere else on same sheet, I get following msg box:

"To prevent possible loss of data, MS Excel canot shift non-blank rows off the worksheet. Try to delete or clear the cells to the right and below your data. Then select cell A1, and save your workbook to reset teh last cell used. Or, you can move the data to a new location and try again."

Doesn't work ... worksheet is only 774 rows, and goes up to col AU. I have tried deleting all the rows below and cols to the right - no difference.
Turning auto-filters off doesn't make any difference either.

Only workaround I've found is to create a new row by selecting a whole region, cutting and moving one row down, and then pasting the row I want to move in the empty slot, but it't taking me so much longer ... I need to move lots of rows!

Thanks for any help!!
Regards,
Dave

Re: Coding 'Find All' function in VBA

Hi,

Thanks for responding ... all the data is in 1 column on the one sheet, so hopefully that will make it easier.

I don't think a filter would work as it will just be used to search for a key word which could be anywhere in any of the cells.

Basically, I've got a huge list of FAQs that I need to make available to people, who can then quickly search through them using a key search word.
Using Excel as I know more about it than anything else (tho not as much as I thought I did!) but if you can suggest an easier alternative - Word, html doc. or etc then cool.

This will teach me to commit to doing something at work before checking that I actually know how to do it!!

Regards,
Dave

Hi,
I need to design a macro which will basically automate the "Find All" function so that other users can perform a search on a sheet.

e.g. equivalent to going Edit, Find, "search term", Find All, and then being able to click on the desired result from the list displayed and be taken to that cell.

I've spent about 4 hrs getting nowhere with this - when I try to record a macro of myself performing this action using Edit,Find the macro is blank and contains no code at all. If I record using "Find Next" instead of Find All, there is code but a) it doesn't actually execute and take you to the cell when you run it by macro and b) I really need the list of results that users can choose from.

Any help greatly appreciated...