Posts by Binning

Re: If Row 1 = Row 2 then delete row 2, else msgbox

I'm not too sure on the rules for bumping here but this post has found itself on page 2 so I'm afraid it'll be ignored

Re: xlDown based on previous column

I've managed to solve this.

Hi there,

I'm trying to replicate a formula into a newly created column based on the number of entries in the previous column. The formula currently looks like this:

Sub InsertMatch()
    Columns("D:D").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("D2").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(ISNA(MATCH(RC[-1],OBOB.NGP_Data!C[-1],0)),0,MATCH(RC[-1],OBOB.NGP_Data!C[-1],0))"
    Range("D2").Copy
    Range("C2").Select
    Range(Selection, Selection.End(xlDown)).Offset(0, 1).Paste
    
End Sub

However I keep returning the error "object doesn't support this property or method"

Any suggestions?

Re: Cut unnecessary data and paste in other tab

I've solved this using filters but I'm not sure this is the most effective way of doing it.

Sub MoveValues()
    Selection.AutoFilter
    ActiveSheet.Range("$A$1:$AP$1231").AutoFilter Field:=4, Criteria1:="<>0" _
                                        , Operator:=xlFilterValues
    Range("A2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.SpecialCells(xlCellTypeVisible).Copy
    Sheets("deleted data").Select
    Range("A" & Rows.Count).End(xlUp).offset(1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
If Selection.Cells(1, 1).offset(-1, 0).Interior.ColorIndex = 35 Then
    Selection.Interior.ColorIndex = 0
   Else
    Selection.Interior.ColorIndex = 35
End If
    Sheets("SOD_Data").Select
    Selection.SpecialCells(xlCellTypeVisible).EntireRow.Delete
    Cells.Select
    Selection.AutoFilter
Range("A1").Select
End Sub

Hi there,

I'm looking for a macro that searches column D for all values above 0, selects the entire rows, then moves the rows to the next available cell on a seperate sheet. The macro I'm currently using is:

Dim LR As Long, i As Long
Sheets("SOD_Data").Activate
    LR = Range("D" & Rows.Count).End(xlUp).Row
    For i = 1 To LR
        If Range("D" & i).Value > 0 Then Range("D" & i).EntireRow.Select
    Next i
Selection.Copy
Sheets("deleted data").Select
Range("A" & Rows.Count).End(xlUp).offset(1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
If Selection.Cells(1, 1).offset(-1, 0).Interior.ColorIndex = 35 Then
    Selection.Interior.ColorIndex = 0
   Else
    Selection.Interior.ColorIndex = 35
End If
End Sub

However this only copies the last row as oppposed to all of them. I would also like it to delete the rows on sheet "SOD_Data" once they have been copied across.

Any ideas? I also need all the new data that's being copied across to follow the color index logic (i.e. if last range was white, this new range will be green and vice versa).

Hi there,

I'm trying to input a check into my macro. Basically the logic I'm trying to follow is:

If Row 1 = Row 2 then delete row 2,
ElseIf Row 1 <> Row 2 then Display MsgBox "Row 1 and Row 2 are different. Do you wish to continue?"
If user selects yes, resume
If user selects no, end sub and display MsgBox "Macro ended. Please correct changes manually"

Can anyone help me if this isn't too much trouble?

Thanks,
Binning

Re: Colouring recent data

I've managed to figure this out on my own if anyone is interest. It was actually a lot more simple than I previously thought. This changes it to light green.

Sub ChangeColour()


If Selection.Cells(1, 1).offset(-1, 0).Interior.ColorIndex = 35 Then
    Selection.Interior.ColorIndex = 0
   Else
    Selection.Interior.ColorIndex = 35
End If
    
End Sub

Hi there,

I'm currently running a macro which takes data from one workbook containing information on a specific day and inputs it into a master document containing information for all days. Currently, when the information is added to the new file the standard procedure is to change the ColorIndex of the new range to a light green if the last entry's ColorIndex was white, and vice versa.

Currently the only macro I can come up with changes the background colour to a random colour using the following:

Sub ColorChange()


Dim myvalue


myvalue = Int ((56 * Rnd) + 1)


Selection.Interior.ColorIndex = myvalue


End sub

This is fine but starts to look very untidy and very unprofessional with the colours being all over the place and some darker than others, making the text hard to read. Basically I need something that checks the ColorIndex of the cell above. Cell above is currently defined as:

Selection.Cells(1, 1).Offset(-1, 0).Select

i.e. the cell above the top left cell in the current selection.

If the ColorIndex is white, the newly pasted range will be changed to a light green. If the ColorIndex of the cell above is a light green, the newly pasted range will be changed to white.

I hope this isn't too much trouble.

Cheers,
Binning

Re: Unprotect sheet, multiple password combinations

I would also like to mention that I'll be unprotecting two sheets, not sure what difference this makes.

Re: Unprotect sheet, multiple password combinations

Hi Roy,

I'm not sure what's happening but when I run the macro with your code nothing seems to be happening, however when I have ActiveSheet.Unprotect Password:-"aDMIN" (which is the password on this sheet currently), everything runs fine. Any suggestions? Also, I don't need to include 'On Error Resume Next' throughout the macro, it was only included for the errors associated with a wrong password entry.

Thanks again

Hi there,

Currently looking for a macro which tries a combination of three different passwords to unlock a sheet. The password can either be 'Admin' 'ADMIN' or 'aDMIN'. If all of these fail then I would like a prompt box asking the user to input the password manually.

Can anyone help me here?

Currently just running 'On Error Resume Next', however this will resume the macro even if the next combination of passwords are incorrect.

Thanks,
Binning

Re: Replicate Text Alphabetically

I've figured out two long winded ways of doing it. The first way is using a custom list which doesn't exactly suit me.

For anyone else who is interested, the second idea was ="XXXX"&CHAR(ROW(A1)+64)

If anyone else has an easier way I'd be very happy to know.

This should be an easy question.

I know that when you replicate numbers down rows on excel it will ascend/descend depending on the pattern before. For instance, if I have "1" in A1 and "2" in A2, if I replicate it down to A4 it will go 1,2,3,4. However, I was wondering if this feature was possible with letters, such that I could have:

"XXXA"
"XXXB"
"XXXC"

All through the alphabet without having to insert these manually.

Any suggestions? Right now when I replicate it down it will just copy the cell contents.

Re: Loop copy and paste until final sheet

Stephen,

Thanks for the response but I'm having trouble with the layout of the workbooks I'm working from. So far as I can see this is taking information from every worksheet in the workbook, however I only want to take information from a number of them. It's also proving difficult as there are a number of hidden sheets.

I think going forward I'm just going to use the original macro, but thanks for the help!

Re: Find on Workbook

I've now noticed that when I unmerge the cells, it finds the correct result. However for the sake of going forward the cells will always be merged together. Any ideas?

EDIT: Nevermind, I've found another unique identifier which is unmerged in the worksheet I require.

Thanks guys, formulas now work!

Re: Find on Workbook

I don't know what's causing this issue, but when I change "IFDS" to another constant nothing happens. However, when I use excel's standard search function (CTRL + F), and run a search on the whole workbook, it returns the first value just fine.

Is there any reason for this? Is it worth mentioning that the other word is part of a merged cell?

Re: Find on Workbook

Hi Chirayuw,

This works however it lands on the last IFDS that it found. Is there any way to exit the sub after the first value has been returned?

Re: Loop copy and paste until final sheet

Hi Stephen,

Getting the following error:

PasteSpecial method of Worksheet class failed.

Hi there,

I'm currently running this macro:

Sub CopyNextTab()
     
WorkbookName = ActiveWorkbook.Name


Cells.Select
Selection.Copy
Windows("Test.xlsm").Activate
    Sheets("Pasted Data").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        
Sheets("Calculations").Select
Range("A2:K40").Select
Selection.Copy
Sheets("Master").Select
lMaxRows = Cells(Rows.Count, "B").End(xlUp).Row
Range("A" & lMaxRows + 1).Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
     
Workbooks(WorkbookName).Activate
    If ActiveSheet.Index <> ActiveWorkbook.Sheets(ActiveWorkbook.Sheets.Count).Index Then
        ActiveSheet.Next.Activate
    Else
        Exit Sub
    End If
     
End Sub

I'm not entirely sure if this is correct, but what I'm looking to do is the following:

1. On the currently selected sheet, copy all data into the "Pasted Data" sheet on "Test.xlsm"
2. On the "Calculations sheet", copy all information across to the next available row on "Master Sheet"
3. Select the original Workbook and move to the next tab
4. Repeat until there are no more tabs remaining

So far as I can tell steps 1-3 are working (however I'm not entirely confident with my code to move to the next sheet as I'm not sure it will end the sub on the last sheet). Basically I just need help looping this until the information on the final tab has been copied across.

Thanks!
Binning

Hi there,

I've had a browse around the forums and most of the code already posted for what I'm trying to do requires a message box to be filled in to query what the user is looking for. However, in this situation the word I'm looking for will be constant.

All I need to be able to do is run a search on all sheets for the word "IFDS" and then for the macro to activate the first sheet with that word on it.

Preferably I would like the macro to find the first value, select the entire sheet, copy the sheet to a new workbook, find the next value and repeat until all "IFDS" values have been found, however if this is too complex then the first requirement will be fine.

Thanks,
Binning