Posts by matt_boy

Re: Locking select worksheets

Format=>Cells=>Protection=>Locked

You could just make all cells on the worksheet locked apart from the ones you require to change, then protect the worksheet.

Re: Run Time 1004 Error

Hi

could you not do this i.e make the sheet visible whilst the code is running then unhide again? If you turn off screen updating then it still cannot be seen whilst the sode is running:

Application.ScreenUpdating = False
Worksheets("YTDdb").Visible = xlSheetVisible
Worksheets("YTDdb").Activate
       'your code i.e.set etc etc


Worksheets("YTDdb").Visible = xlSheetHidden


Application.ScreenUpdating = True

Hope that helps

Re: Doiing a loop based on the number of non-empty rows

Would it not be better to find the last row that has something entered into it then loop through all the rows above? You can then loop through all these rows and if nothing is entered in a particular row, dont copy that row etc. If this is the case then you require something like this:

Sub macro1()


    Dim LastRow As Integer
    Dim I As Integer
    Dim rrng As Integer
    
    Application.ScreenUpdating = False
    
    LastRow = Range("A65536").End(xlUp).Row
     
    For rrng = 2 To LastRow
        With Cells(rrng, 1)
            
            'insert code here
            
        End With
    Next rrng
     
    Application.ScreenUpdating = True
    


End Sub

If you require any help on this just let me know.

Hope that helps

Re: Xl

This what you mean?

Hope it helps

Re: combining macros

Try this

Private Sub CommandButton1_Click()
run: macro1
run: macro2
run: macro3


'etc etc


End Sub

Hope that helps

Re: If statements

Copy this statement and paste into C1

=IF(AND(A1=0,B1 = 0),0,IF(AND(A1=1,B1 = 1),1,IF(AND(A1=1,B1 = 0),0,IF(AND(A1=0,B1 = 1),0,""))))

Hope this helps

Re: Toggle Format

This works over a selection of cells that contain etiher type of formatting i.e if the selection contains blue cells they will turn white and white cells will turn blue:

Sub BlueShading()
     
Dim crng As Range


Set crng = Selection
     
     For Each crng In Selection
    If crng.Font.Color = RGB(255, 255, 255) Or Selection.Font.ColorIndex = -4105 Then 'White or Automatic
         
        crng.Font.Color = RGB(0, 0, 0)
        crng.Interior.ColorIndex = xlNone
        crng.Font.Bold = False
         
    ElseIf crng.Font.Color = RGB(0, 0, 0) Then 'Black
         
        crng.Font.Color = RGB(255, 255, 255)
        crng.Interior.Color = RGB(0, 28, 92)
        crng.Font.Bold = True
         
    End If
    Next crng
     
End Sub

Hope this helps

Re: Toggle Format

It seems to work for me, but only when the selection contains only one style of formatting, if your selection contains both white and blue it doesnt work, was this what you were trying to achieve?

Re: Find and replace query

No Will not at all, I appreciate the help, I myself am fairly new to VBA. For a relative novice like myself, it is just nice to have code that works! I appreciate the help in understanding good coding techniques. I am teaching myself at the moment pretty much from scrath using this website, so the only way I will learn is if you guys explain why certain methods are used as opposed to others. It is very nice to have the most efficient code possible however being new it is near on impossible to know what is best.

Re: Search range, find value, copy then offset paste across 2 worksheets

I am finding it difficult to understand what you actually require?
Could you post a small example or elaborate a little?

Re: Open save as dialog box using macro.

Do you mean this?

Sub opensavedialog()


Application.Dialogs(xlDialogSaveAs).Show


End Sub

Hope this helps

Matt

Re: Find and replace query

Ammended above code slightly in order to get what u stated:

Sub age_bander()
     
Application.ScreenUpdating = False


     
i = 2


While i < 1600
     
    Columns("d:d").Select
    Range("d" & i).Activate
     
    If ActiveCell.Value = "1" Then
        ActiveCell.Value = "Under 18"
    ElseIf ActiveCell.Value = "2" Then
        ActiveCell.Value = "18 - 64"
    ElseIf ActiveCell.Value = "3" Then
        ActiveCell.Value = "65 - 74"
    ElseIf ActiveCell.Value = "4" Then
        ActiveCell.Value = "75+"
    ElseIf ActiveCell.Value = "5" Then
        ActiveCell.Value = "Age unknown"
   
    End If
    


    i = i + 1
         
    Wend
         
Application.ScreenUpdating = True


End Sub

Hope this helps

Re: Find and replace query

IS this any good?

Sub age_bander()
     
Application.ScreenUpdating = False


     
i = 2


While i < 1601
     
    Columns("d:d").Select
    Range("d" & i).Activate
     
    If ActiveCell.Value = "1" Then
        ActiveCell.Value = "Under 18"
        
    End If


    i = i + 1
         
    Wend
         
Application.ScreenUpdating = True


End Sub

Hope this helps

Matt

Re: Compare two colums to eachother

Hi again

removing the file extensions worked and the code will run now!! Happy days

Thanks for the help batman however it would be mostly appreciated if you could still post the example using conditional formatting as I may decide to use this instead and it will help my knoledge of excel. I can get it to work on the same sheet however referencing another sheet seems to cause problems.

All your help is greatly appreciated

Matt

Just got your example as i posted this will let you know if i get any other problems

Once again thanks v much

Re: Compare two colums to eachother

Hi Batman

I tried using the code you said but I still coundn't get it to work :?

So then I tried the Conditional Formatting and was getting that to work however when I tried to use other worksheets (like i was doing in the coded version) excel told me I may not use references from other worksheets or workbooks for conditional formatting which blows that option out of the water (which is kinda unforunate because a lot easier), even though it allows you to input reference to other workheets. So now Im back to my original problem. Is there any way you could post what you said you got to work?

This is what I did with your code, sorry if you think im being stupid!!

Option Explicit
Dim cCola As Range
Dim cColb As Range
Dim rngIRd As Range
Dim rngACs As Range
Dim Match As Boolean


 
Sub FindDubs()
    
Set rngIRd = Workbooks("Invoice Check.xls").Worksheets("invoices recieved").Range("A3:A10")
Set rngACs = Workbooks("Invoice Check.xls").Worksheets("All Cons").Range("A3:A10")
    
    For Each cCola In rngIRd
        Match = False
        For Each cColb In rngACs
            If cCola = cColb Then
                Match = True
                cColb.Interior.ColorIndex = 4
                cCola.Interior.ColorIndex = 4
            End If
        Next cColb
        If Match = False Then
           cCola.Interior.ColorIndex = 3
        End If
    Next cCola
     
End Sub

Just gettin the same error as before

Thanks v much

Matt

Re: Compare two colums to eachother

Help needed again!!!!

The colums I'm using are on 2 different sheets and for some reason I can't seem to get this to work. Sorry if I'm being stupid, I am trying!! Here is what I've tried to do on top of Bills code.

Option Explicit
Dim cCola As Range
Dim cColb As Range
Dim rngIRd As Range
Dim rngACs As Range
Dim Match As Boolean


 
Sub FindDubs()
    
Windows("Invoice Check.xls").Activate
    Worksheets("invoices recieved").Activate


Set rngIRd = Range("A3:A10")


Windows("Invoice Check.xls").Activate
    Worksheets("All Cons").Activate


Set rngACs = Range("A3:A10")
    
    For Each cCola In rngIRd
        Match = False
        For Each cColb In rngACs
            If cCola = cColb Then
                Match = True
                cColb.Interior.ColorIndex = 4
                cCola.Interior.ColorIndex = 4
            End If
        Next cColb
        If Match = False Then
           cCola.Interior.ColorIndex = 3
        End If
    Next cCola
     
End Sub

It keeps coming up with runtime error 1004
Unable to set the colourindex property of the interior class

Please could someone let me know what I'm doing wrong and explain it to me, it's doing my head in :yikes:

Many many thanks

Matt

By the way if anyone could let me know how to improve on my code, I know it properly isn't written in the best way, then please let me know. I know there are meant to be certain methods which are deemed as good practice however i don't know what these are (I'm only a beginner to VB). Thanks

Re: Compare two colums to eachother

Hi Gents

Thanks ever so much for the suggestions!! Tried both methods and couldnt get the conditional formatting to work (not too sure what i was doing wrong but I dont doubt it was me getting it wrong!) however the code worked a treat with no changes. Apologies if my question was none too clear originally. Really appreciate the help though.

Thanks Bill and Batman!!!!!

Matt

Hi all again

Quick one (I hope).... If i have two columns, what would be the best way to compare all the values in these two columns and if any values exist in both columns, albeit anywhere in these columns, then the cells in both columns are changed to green, whereas any cells that dont exist in both columns are changes red? Any suggestions? I can't think how to go about this.

Many Thanks in advance

Matt

Re: Force a cell to show entered text in Upper case

You could either use a font that is only a caps font or alternatively use the formula:

=UPPER([insert text or cell ref here])

Re: Range Select

Thanks guys, spent ages playing about with the quotes driving myself mental.

:thanx: