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
 

Some videos you may like

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,782
Office Version
  1. 2010
Platform
  1. 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,782
Office Version
  1. 2010
Platform
  1. 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:
 

Watch MrExcel Video

Forum statistics

Threads
1,122,959
Messages
5,599,056
Members
414,281
Latest member
Engjamal2021

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top