Posts by Mumps

    Re: Object required error


    Thank you again. I did remove "set" but now I get a 'Range of Object Global Failed" error. I tried putting an "On Error Resume Next" statement in my original code as below and now it seems to work properly.


    [VB]
    Dim rInput As Range
    Dim sAddress As String
    On Error Resume Next
    Set rInput = Application.InputBox _
    (prompt:="Enter the Cell Address of the points you wish to delete ... e.g. C2.", _
    Title:="Delete Points", Type:=8)

    If rInput = "" Then
    MsgBox ("You have not entered a Cell Address. Please try again.")
    Exit Sub
    End If

    sAddress = rInput.Address
    Range(sAddress).ClearContents
    [/VB]

    Re: Object required error


    Thank you so much for your quick response. I changed rInput to a string and modified the last line but now I get an Object Required error on the Set rInput line.

    I have the following code to clear the contents of a cell based on user input. I want to exit the sub if the Cancel button is clicked but I get an Object Required error. Any help would be terrific. Thanks.


    [VB]
    Dim rInput As Range
    Dim sAddress As String
    Set rInput = Application.InputBox _
    (Prompt:="Enter the Cell Address of the points you wish to delete ... e.g. C2:", _
    Title:="Delete Points", Type:=8)

    If rInput = "" Then
    MsgBox ("You have not entered a Cell Address. Please try again.")
    Exit Sub
    End If

    sAddress = rInput.Address
    Range(sAddress).ClearContents
    [/VB]

    Re: Count number of occurrences on cell exit


    Thank you, Mike, for your quick reply. I am entering data down Column B starting at Row 4. As I type the data in each cell and press Enter or the TAB key, I want to check all of Column B to get a count of how many times I entered that same data and post a message when the count is greater than 8.

    I am trying to write some code that will count the number of occurrences in a column (B4:B123) of the text entered in a cell once the cell is exited. I would also like a message to be displayed if the number is greater than 8. I have tried the Worksheet_Change event in combination with CountIf but I can't get it quite right. Any help would be appreciated.

    Re: Extract data from unopened workbooks


    Hello Smallman. There was nothing wrong with what you gave me and I certainly appreciate it. Please accept my apologies if I offended you. The fault was with me for posting sample data and not my actual data. I have not been able to make it work with my actual data. That is why I posted again. Again, thank you for your help,

    I have been trying to get some help on how to extract data from closed workbooks and placing that data into another open workbook. The attached workbook has four sheets. In reality, these four sheets would actually be in four different files with different names. I've put them into one workbook to be able to upload a small enough file. I would like to take the data from each of the first three sheets (files) and populate sheet four with the data so that it contains all the data from the first three sheets. The format and structure is identical in all the sheets (files). I was able to get some valuable help from member Wigi and this is much appreciated. The code he gave me is below. Unfortunately, it doesn't quite work in my situation, not because of the code he offered because it was excellent advice, but because I wasn't clear enough in my original posting. I've done some research to try to find a solution, but I haven't had much success. Any help would be greatly appreciated.


    [VB]
    rng = "A1:G238"
    fold = ThisWorkbook.Path & "\"

    With Range(rng)
    sq = .Value
    For i = 1 To 3
    sh = "Sheet" & i
    fl = "book" & i & ".xlsx"
    fmula = "'" & fold & "[" & fl & "]" & sh & "'!" & Split(.Address(0, 0), ":")(0)
    .Formula = "=if(" & fmula & "="""",""""," & fmula & ")"
    For ii = 1 To .Count
    If Len(.Cells(ii)) Then sq(ii, 1) = .Cells(ii)
    Next
    Next
    .Value = sq
    End With
    [/VB]

    Files

    Re: Merge/join data from sheets in different files into one sheet


    Hello again. I have been working with your advice with the actual data and I think that I'm almost there. I have attached a file with the real data. I would like to copy the data from the first 3 sheets to the RegionLanes sheet. I ran the following version of the macro you sent me. It appears to copy the data from the first sheet and then I get an error (subscript out of range). I'm not sure how to fix the problem. I'm still learning VBA and I appreciate your help and patience.


    [VB]
    Sub ImportFormula()

    rng = "A1:G238"
    fold = ThisWorkbook.Path & "\"

    With Range(rng)
    sq = .Value
    For i = 1 To 3
    sh = "Sheet" & i
    fl = "book" & i & ".xlsx"
    fmula = "'" & fold & "[" & fl & "]" & sh & "'!" & Split(.Address(0, 0), ":")(0)
    .Formula = "=if(" & fmula & "="""",""""," & fmula & ")"
    For ii = 1 To .Count
    If Len(.Cells(ii)) Then sq(ii, 1) = .Cells(ii)
    Next
    Next
    .Value = sq
    End With

    End Sub[/VB]

    Files

    Re: Merge/join data from sheets in different files into one sheet


    Smallman and Wigi, thank you so very much for your help. I ran Wigi's macro and it worked well except that it copied the data from the first three sheets into sheet1. The macro should leave the first three sheets unchanged and the data from each should be copied to sheet4.

    Re: Merge/join data from sheets in different files into one sheet


    Thank you very much Smallman. I would like to have all the data copied to column B in the Consolodation worksheet in Range("B1:B8"). Would that be possible? Is "book" with the variable "i" the file name? This would mean that I would replace "book" with the file name that I am using. Would that be correct? I apologize that I wasn't clear in my original posting. Thank you again.

    The attached file has four sheets that I have placed into one workbook to keep the file as small as possible. In reality, each sheet is in a separate file. The format of each sheet is identical. I would like to merge/join the data from the first three sheets (files) into the last sheet. If possible, it would be helpful if this could be done without having to open the first three files. Any suggestions would be greatly appreciated.

    Files

    • LanesTest.xls

      (29.18 kB, downloaded 61 times, last: )

    I want to clear the contents of cells with no color. When I run the following code, I get "Cannot change part of a merged cell." error. Any suggestion would be greatly appreciated. Thank you.


    [VB]
    Dim c As Range
    For Each c In ActiveSheet.UsedRange
    If c.Interior.ColorIndex = xlNone Then c.ClearContents
    Next c
    [/VB]

    Re: Loop through range to find a value-copy paste to another sheet


    I have worked on the problem and have come up with the code below that works. If you have any other suggestions that might work better, that would be great! Thank you for your help.


    [VB]
    Range("A2").Select
    Selection.AutoFilter
    ActiveSheet.Range("$A$1:$G$241").AutoFilter Field:=1, Criteria1:= _
    "RELAY Grade 4"
    ActiveSheet.Range("$A$1:$G$241").AutoFilter Field:=2, Criteria1:="G"

    Dim FirstCell As Integer
    Dim LastCell As Integer
    FirstCell = Range("G2", Cells(Rows.count, "G").End(xlUp)).SpecialCells(xlCellTypeVisible).Cells(1, 1).Row
    LastCell = Range("G2", Cells(Rows.count, "G").End(xlUp)).SpecialCells(xlCellTypeVisible).Cells(5, 1).Row

    Dim x As Integer
    For x = FirstCell To LastCell
    If Range("G" & x).Value = 1 Then
    Sheets("RegionLanes").Range("B6").Value = ActiveSheet.Range("G" & x).Offset(0, -2).Value
    End If
    If Range("G" & x).Value = 2 Then
    Sheets("RegionLanes").Range("B8").Value = ActiveSheet.Range("G" & x).Offset(0, -2).Value
    End If
    If Range("G" & x).Value = 3 Then
    Sheets("RegionLanes").Range("B9").Value = ActiveSheet.Range("G" & x).Offset(0, -2).Value
    End If
    Next x
    [/VB]

    Files

    • TestFile.xls

      (100.35 kB, downloaded 169 times, last: )

    I have some code that filters the data in a worksheet, searches the cells in one of the columns for a value and then if a match is found, copies the value of the cell two columns to the left to another sheet. I am searching for the values 1, 2 or 3. Could someone help me in completing this macro to look for each of the three numbers and do the copy if they are found. I have tried a few things but I can't seem to get it it to work properly. This is what the code looks like:


    [VB]
    Range("A2").Select
    Selection.AutoFilter
    ActiveSheet.Range("$A$1:$G$241").AutoFilter Field:=1, Criteria1:= _
    "RELAY Grade 5"
    ActiveSheet.Range("$A$1:$G$241").AutoFilter Field:=2, Criteria1:="G"


    Dim FirstCell As Integer
    Dim LastCell As Integer
    FirstCell = Range("G2", Cells(Rows.count, "G").End(xlUp)).SpecialCells(xlCellTypeVisible).Cells(1, 1).Row
    LastCell = Range("G2", Cells(Rows.count, "G").End(xlUp)).SpecialCells(xlCellTypeVisible).Cells(5, 1).Row


    Dim Cell As Range
    For Each Cell In Range("G" & FirstCell & ":G" & LastCell)
    If Cell.Value = 1 Then Cell.Offset(0, -2).Select
    Selection.Copy
    Sheets("RegionLanes").Range("B6").PasteSpecial (xlPasteValues)
    Next Cell
    [/VB]

    Re: Copy and paste rows based on multiple criteria


    Thank you once again AAE. I changed the code only to make it easier for me to identify the sheets. The code you sent me does work perfectly in the sample workbook but for some reason it doesn't in my actual workbook which contains 12 worksheets. The sheet names are the same in both. I included only 2 sheets in the sample otherwise the file would have been too large to upload. I will continue to work with it. I am relatively new at using VBA and it has become my hobby. I truly appreciate your help and your patience.

    Re: Copy and paste rows based on multiple criteria


    My apologies. The problem was caused by a typo. I have fixed the code tag. Thank you so much for you suggestion, AAE. I modified your code as below and ran through it one line at a time. It worked as expected except that it didn't copy the rows to the RegionQualifiers sheet. I'm not sure what went wrong.


    [VB]
    Option Explicit

    Sub Copy_Cells()

    Dim nextrow As Long, lastrow As Long
    Dim rngData As Range

    Application.ScreenUpdating = False
    Sheets("TrackA").Unprotect

    nextrow = Sheets("RegionQualifiers").Cells(Rows.count, "A").End(xlUp).Row + 1

    With Sheets("TrackA")
    .AutoFilterMode = False
    lastrow = .Cells(Rows.count, 1).End(xlUp).Row

    .Range("H1") = "Helper"
    .Range("H2:H" & lastrow).Formula = "=OR(LEFT(A2,FIND("" "",A2,1)-1)={""800M"",""1500M"",""Relay""})"

    Set rngData = .Range("A1:H" & lastrow)

    rngData.AutoFilter field:=3, Criteria1:=">=1", Operator:=xlAnd, Criteria2:="<=3"
    rngData.AutoFilter field:=8, Criteria1:=False

    rngData.Resize(, 7).SpecialCells(xlCellTypeVisible).Copy Sheets("RegionQualifiers").Range("A" & nextrow)

    Application.CutCopyMode = False

    .AutoFilterMode = False

    .Range("H1:H" & lastrow).Clear

    End With

    Set rngData = Nothing

    Application.ScreenUpdating = True

    End Sub
    [/VB]