VB: run-time error '1004': Application-defined or object-defined error

[ABS]

Hi,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>

I just started using VB to create macros in Excel recently and I could really use some help.<o></o>
<o></o>

This macro is intended to go through a column of identifiers in one worksheet (OrgNameMatches) and for each cell search if there is a matching identifier in another column in another worksheet (OrgFacilityRelationship) of the same file. If there is a match, then a hyperlink is to be created between the identifier from OrgNameMatches to the identifier in OrgFacilityRelationship.<o></o>
<o></o>

I have the following code so far… (The Run-time error '1004': Application-defined or object-defined error occurs after the following lines:<o></o>
<o></o>

Sheets("OrgFacilityRelationship").Select <o></o>
Range("A2").Select lines<o></o>
<o></o>
<o></o>
<o>

Sub orgFacilityLink()
 
Sheets("OrgNameMatches").Activate
Sheets("OrgNameMatches").Select
Range("A2").Select
 
Do Until Selection.Offset(1, 0).Value = ""
 
varIdMatch = False
varIdValue = ActiveCell.Value
varIdRow = ActiveCell.Row
 
Sheets("OrgFacilityRelationship").Activate
Sheets("OrgFacilityRelationship").Select
Range("A2").Select
 
Do Until Selection.Offset(1, 0).Value = ""
 
If Selection.Value = varIdValue Then
 
varIdMatch = True
varIdMatchRow = ActiveCell.Row
Exit Do
 
End If
 
Selection.Offset(1, 0).Select
 
Loop
 
Sheets("OrgNameMatches").Select
Range("A" & varIdRow).Select
 
If varIdMatch = True Then
 
ActiveCell.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:="Sheet3!A" & varIdMatchRow, TextToDisplay:=varIdValue
 
End If
 
Selection.Offset(1, 0).Select
 
Loop</o:p>
<o:p>End Sub

</o>
<o>
</o>
<o></o>
If anyone can please help to resolve the run-time error or if you can point out any other problems with my code, I would greatly appreciate it. Thanks!<o></o>

 

[wigi]

Sheets("OrgFacilityRelationship").Select
Range("A2").Select

You're sure that you do not have a typo in that sheet name?
That sheet exists in the Active Workbook?

 

[Peter_SSs]

I think you should check where you have that code.
That error looks to me like it would result if you had the code in the OrgNameMatches sheet module.
As it stands, the code would need to be in a standard module, not a worksheet module.

 

[ABS]

Yes, that was exactly the problem. Thank you very much for your help. Works like a charm now.

 

[Peter_SSs]

Yes, that was exactly the problem. Thank you very much for your help. Works like a charm now.

No problem, and thanks for the feedback.

Also, a belated 'Welcome' to the MrExcel board!

 

[JennyE42]

Thank you very much! I also found this very helpful!

I think you should check where you have that code.
That error looks to me like it would result if you had the code in the OrgNameMatches sheet module.
As it stands, the code would need to be in a standard module, not a worksheet module.

 

[Luka_LB]

Hello,

I'm facing same problem

I had excel file with working code, I copied that file and updated that code in new file
after that i get this 1004 error code although code are almost the same

If ThisWorkbook.Sheets("data").Range("BA").Value = "0" Then
If ThisWorkbook.Sheets("data").Range("BA").Value < 10 Then


If ThisWorkbook.Sheets("data").Range("BA").Value = "10" Then
If ThisWorkbook.Sheets("data").Range("BA").Value = "11" Then
If ThisWorkbook.Sheets("data").Range("BA").Value = "12" Then


If ThisWorkbook.Sheets("data").Range("BA").Value = "20" Then
If ThisWorkbook.Sheets("data").Range("BA").Value = "21" Then
If ThisWorkbook.Sheets("data").Range("BA").Value = "22" Then


If ThisWorkbook.Sheets("data").Range("BA").Value = "30" Then
If ThisWorkbook.Sheets("data").Range("BA").Value = "31" Then
If ThisWorkbook.Sheets("data").Range("BA").Value = "32" Then


If ThisWorkbook.Sheets("data").Range("BA").Value = "40" Then
If ThisWorkbook.Sheets("data").Range("BA").Value = "41" Then
If ThisWorkbook.Sheets("data").Range("BA").Value = "42" Then

I have these IF's with their own else and so on
but all the ifs give me error

Run-time error '1004'
Application-defined or objected-defined error

in the first file i had ifs checking values in different sheets and it works
but i had to move all those cells to one folder, and it doesn't work.

any ideas why is that?

 

[Luka_LB]

found what was missing

i had indicated only cell column and forgot the row

Range("BA") instead of Range("BA1") </pre>

 

[benslocs]

Having a problem with the below code.

It is in a module and called in the sheet.

I get the '1004' error on line 'Newsh.Cells(RwNum, ColNum).Formula = _ "='" & sh.Name & "'!" & myCell.Address(False, False)'

Any help would be greatly appreciated.

Sub Macro1()


    Sheets("Database").Unprotect Password:="password"


    Dim sh As Worksheet
    Dim Newsh As Worksheet
    Dim myCell As Range
    Dim ColNum As Integer
    Dim RwNum As Long
    Dim Basebook As Workbook


    Set Basebook = ThisWorkbook
    Set Newsh = Basebook.Worksheets("Database")
  
    'The links to the first sheet will start in row 11
    RwNum = 10


    For Each sh In Basebook.Worksheets
        If (sh.Name <> Newsh.Name And sh.Name <> "Guidance" And sh.Name <> "Database" And sh.Name <> "Analysis TEMPLATE") And sh.Visible Then
            ColNum = 1
            RwNum = RwNum + 1
            'Create a link to the sheet in the A column
            Newsh.Hyperlinks.Add Anchor:=Newsh.Cells(RwNum, 1), Address:="", _
            SubAddress:="'" & sh.Name & "'!A1", TextToDisplay:="Ref. " & sh.Name


            For Each myCell In sh.Range("C6, E6, L6, O6, Q6, C9")
                ColNum = ColNum + 1
                Newsh.Cells(RwNum, ColNum).Formula = _
                "='" & sh.Name & "'!" & myCell.Address(False, False)
                
            Next myCell


        End If
    Next sh


    Newsh.Range("D11:D1048576").NumberFormat = "£#,##0.00"
    Newsh.Range("D11:E1048576").HorizontalAlignment = xlLeft
    ActiveWindow.DisplayGridlines = bGridOff
    
    Range("A11:A1048576").Font.ColorIndex = 1
    
    Newsh.Range("A1:G1048576").Locked = True
    Newsh.Range("C5:C7").Locked = False
    Newsh.Protect Password:="password"
    
    Sheets("Database").Move before:=Sheets("1")
    
End Sub

 

[jorgan202]

Hi, this code works for the first 8 sheets, anything thereafter comes up with the thread title? any ideas please. [The code is in each sheet module and unique i.e. "Sheet05.PA05" will read "Sheet06.PA06" and the Sub name will change from PA05 to PA06 and so on till sheet 25].

Private Sub CommandButton2_Click()
Application.Run "Sheet05.PA05"
End Sub

Sub PA05()

Dim fnd As String, FirstFound As String, counter  As Integer
Dim FoundCell As Range, rng As Range
Dim myRange As Range, LastCell As Range, moduleRange As Range

counter = 3

'What value do you want to find (must be in string form)?
  fnd = Range("A1")

Set myRange = Worksheets("EBS Modules & Data").Range("C3:O24")
Set LastCell = myRange.Cells(myRange.Cells.Count)
Set FoundCell = myRange.Find(What:=fnd, LookAt:=xlWhole, After:=LastCell)

'Test to see if anything was found
  If Not FoundCell Is Nothing Then
    FirstFound = FoundCell.Address
  Else
    GoTo NothingFound
  End If

Set rng = FoundCell

'Loop until cycled through all unique finds
  Do Until FoundCell Is Nothing
    
     With Worksheets("EBS Modules & Data")
        
        Set moduleRange = .Cells(FoundCell.Row, 1)
        moduleRange.Copy Sheet05.Range(Cells(counter, 1), Cells(counter, 1))
        counter = counter + 1
        
     End With
  
    'Find next cell with fnd value
      Set FoundCell = myRange.FindNext(After:=FoundCell)
    
    'Add found cell to rng range variable
      Set rng = Union(rng, FoundCell)
    
    'Test to see if cycled through to first found cell
      If FoundCell.Address = FirstFound Then Exit Do
      
      
  Loop

  
Exit Sub

'Error Handler
NothingFound:
  MsgBox "No values were found in this worksheet"

End Sub