Check value/paste value

line_lina

New Member
Joined
Mar 27, 2011
Messages
6
Hello! I'm a little bit frustrated because this macro is not working. Good thing is that I'm not receiving an error message but it is not working.

I need to change the cell value of the information of Sheet 1 to the cell value of a conversion table at Sheet 2.

Code:
Sub ChangeName()
 

Dim h As Double
Dim g As Long
 

For h = 0 To 300
For g = 0 To 100


 On Error Resume Next
       
        If Sheets("Sheet 1").Cells(h, "B1").Value = Sheets("Sheet 2").Cells(g, "A1").Value Then
        IgnoreBlank = True


        Sheets("Sheet 2").Cells(g, "B") = Sheets("Sheet 1").Cells(h, "B")
                
        Else:  MsgBox "Error!"
          

    End If
 
    Next g
    Next h
    
End Sub
 

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,722
Office Version
2010
Platform
Windows
Good thing is that I'm not receiving an error message but it is not working.
You're not getting an error message because the On Error Resume Next statement says to ignore errors.

There is no row 0 in Excel.

Are there sheets named "Sheet 1" and "Sheet 2"? By default, they would be "Sheet1" and "Sheet2"

This construct is invalid: Cells(h, "B1"). There is no column B1.

What is the code supposed to do?
 

line_lina

New Member
Joined
Mar 27, 2011
Messages
6
Thanks for the quick reply! :)
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:eek:ffice:eek:ffice" /><o:p> </o:p>
I want the code to: check if the cell value at Sheet1/Column B is the same value as a cell in Sheet2/Column A. If the value exists, then I will like to change the values of the cells at Sheet1/Column B for the value of Sheet2/Column B (this column have the new values). Does this make sens/
<o:p> </o:p>
I deleted the On Error Resume Next statement, change the column B1 for B, change the 0 to 1 and fix the sheet names (I have different names at my sheets but I put Sheet 1/Sheet 2 to make it generic), but the codes stays in a loop and it doesn’t do a thing. I even added a message box but I don’t see anything. :eek:

Code:
Sub ChangeName()
 
Dim h As Long
Dim g As Long
 
For h = 1 To 300
For g = 1 To 100
 
'On Error Resume Next
     
        If Sheets("Sheet1").Cells(h, "B").Value = Sheets("Sheet2").Cells(g, "A").Value Then
            IgnoreBlank = True
            Sheets("Sheet1").Cells(g, "B") = Sheets("Sheet2").Cells(h, "B")
            MsgBox "The job is done!"
            
        Else:  MsgBox "Error!"
        End If
 
    Next g
    Next h
    
End Sub
<o:p> </o:p>
<o:p></o:p>
Can you help me? :confused:
 

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,722
Office Version
2010
Platform
Windows
Maybe like this:
Code:
Sub ChangeName()
    Dim wks1        As Worksheet
    Dim wks2        As Worksheet
    Dim cell        As Range
    Dim rLook       As Range
    Dim rFind       As Range
 
    Set wks1 = Worksheets("Sheet1")
    Set wks2 = Worksheets("Sheet2")
    Set rLook = wks2.Columns("A")
 
    For Each cell In Intersect(wks1.Columns("B"), wks1.UsedRange)
        If Len(cell.Text) Then
            Set rFind = rLook.Find(What:=cell.Value, _
                                   LookIn:=xlValues, _
                                   LookAt:=xlWhole, _
                                   MatchCase:=False, _
                                   MatchByte:=False, _
                                   SearchFormat:=False)
            If Not rFind Is Nothing Then
                cell.Value = rFind.Offset(, 1).Value
            End If
        End If
    Next cell
End Sub
 

line_lina

New Member
Joined
Mar 27, 2011
Messages
6
:):):)

WAO! Thanks for the help! It works perfect and super fast!

I'm the happiest person on Earth! :ROFLMAO:
 

Forum statistics

Threads
1,085,714
Messages
5,385,409
Members
401,943
Latest member
xvpnkr

Some videos you may like

This Week's Hot Topics

Top