Update cell values

Microsoft Windows xp inside out, second...
March 9, 2010 at 01:54:37
Specs: Windows XP, pentium 4 3.00Ghz. 1gb ram
I have lot of data in col. A to I running into many rows in sheet2. Also it contains a list in Col.N as shown below:

apple
mango
man
monkey
etc.

Now i want if sheet1 col.A contains mango then its row in Col. B, C, G, J and L's data should be updated whenever i made changes in sheet2 row containing mango where:

sheet2 Col.B = Sheet1 Col.B
sheet2 Col.I = Sheet1 Col.C
sheet2 Col.G = Sheet1 Col.G
sheet2 Col.F = Sheet1 Col.L
sheet2 Col.E = Sheet1 Col.J

For your information:
1. In sheet1 the Figures of sheet2 Col.I, G, F and E is divided by number 100000 using macro (eg. if sheet2 F=1245000 then sheet1 Col.L will be 12.45)
2. I have "Private Sub Worksheet_Change(ByVal Target As Range)" macro in sheet1

Can you provide a macro to accomplish what i want?


See More: Update cell values

Report •


#1
March 9, 2010 at 07:37:44
Just checking...

Do you have your examples backwards?

You said you want Sheet1 updated if Sheet 2 is changed, but then you have:

sheet2 Col.B = Sheet1 Col.B

Which typically means that Sheet2 Col B will be set equal to Sheet1 Col B.

Do you actually mean Sheet1 Col.B = Sheet2 Col.B after changes are made to Sheet2?



Report •

#2
March 9, 2010 at 08:23:32
Assuming the assumption I made in Response # 1 is correct, try this code in the Sheet2 module:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim myText, c
'Get value in Sheet2, Column N, Row in which change was made
 myText = Range("N" & Target.Row)
'Find that value in Sheet1, Column A
  With Sheets(1).Range("A:A")
   Set c = .Find(myText, LookIn:=xlValues)
'If found, update Sheet1 with values from Sheet2
     If Not c Is Nothing Then
       Sheets(1).Range("B" & c.Row) = Sheets(2).Range("B" & Target.Row)
       Sheets(1).Range("C" & c.Row) = Sheets(2).Range("I" & Target.Row)
       Sheets(1).Range("G" & c.Row) = Sheets(2).Range("G" & Target.Row)
       Sheets(1).Range("L" & c.Row) = Sheets(2).Range("F" & Target.Row)
       Sheets(1).Range("J" & c.Row) = Sheets(2).Range("E" & Target.Row)
     End If
  End With
End Sub


Report •

#3
March 9, 2010 at 20:46:43
As always, Thanks a lot DerbyDad03. Thats what I need. Yes Sheet1 Col.B = Sheet2 Col.B after changes are made to Sheet2.

How I can do this for sheet3, sheet4 and sheet5? that is Col.B of sheet1, sheet3, sheet4 and sheet5=Sheet2 Col.B after changes are made to Sheet2. The list(Ranges) could be in: Sheet1 col.A, Sheet3 col.K, Sheet4 col.M and Sheet5 col.P.


Report •

Related Solutions

#4
March 10, 2010 at 04:49:05
Instead of modifying the code for the additional tasks, I'm going to ask that you try it yourself.

The comments in the code should be make it easy to figure out what the code is doing:

'Get value in Sheet2, Column N, Row in which change was made
'Find that value in Sheet1, Column A
'If found, update Sheet1 with values from Sheet2

By replicating the code to search each sheet, one at a time, and update the values, you should be able to get it to do what you want.

In this manner, you'll advance your VBA skills and will soon be offering help in these forums.

If you have specific questions, come on back, but please give it try first.


Report •

#5
March 10, 2010 at 22:37:30
I tried hard but couldn't achieve 100% success.The below code i modified does copy or update changes but at row 19 in sheet3 while corresponding list is in row 8. That is, if mango is in row 8 in sheet3 col K, then when changes are made to sheet2 col.B row containing mango are copied or updated at sheet3 row 19 instead of row 8. Can you see where i did wrong?

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim myText, c
'Get value in Sheet2, Column N, Row in which change was 
made
 myText = Range("N" & Target.Row)
'Find that value in Sheet1, Column A
  With Sheets(1).Range("A:A")
       Sheets(3).Range("K:K")     'I made change here
   Set c = .Find(myText, LookIn:=xlValues)
'If found, update Sheet1 with values from Sheet2
     If Not c Is Nothing Then
       Sheets(1).Range("B" & c.Row) = Sheets(2).Range("B" & 
Target.Row)
       Sheets(1).Range("C" & c.Row) = Sheets(2).Range("I" & 
Target.Row)
       Sheets(1).Range("G" & c.Row) = Sheets(2).Range("G" & 
Target.Row)
       Sheets(1).Range("L" & c.Row) = Sheets(2).Range("F" & 
Target.Row)
       Sheets(1).Range("J" & c.Row) = Sheets(2).Range("E" & 
Target.Row)
       Sheets(3).Range("B" & c.Row) = Sheets(2).Range("B" & 
Target.Row)     'Changed line
     End If
  End With
End Sub


Report •

#6
March 10, 2010 at 23:12:07
You should post the exact code that you tried, because the code you posted in Response #5 won't work at all.

This section causes an error:

With Sheets(1).Range("A:A")
       Sheets(3).Range("K:K")     'I made change here

If I fix that section to read:

With  Sheets(3).Range("K:K")     'I made change here

Then it updates the correct Row in Sheet 3, but the wrong Rows in Sheet1 because mango on Sheet 1 is not in the same Row as mango on Sheet 3.

You can't mix the changes to Sheet 1 with the changes to Sheet 3 because mango might not be in the same row on each Sheet - or is it?

In any case, after fixing the section noted above, I didn't have the "Row 19 vs Row 8" issue you described, but of course, I'm not using your spreadsheet either.


Report •

#7
March 11, 2010 at 00:42:27
Yes mango on Sheet 1 is not in the same Row as mango on Sheet 3. Any fix to the mix will be appreciated. This is the actual code i am using:


Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim myText, c
On Error Resume Next
If Target.Column = 5 Then
    Target.Offset(0, 2).Value = Target.Offset(0, 2).Value + 
Target.Value
Else
'Get value in Sheet2, Column N, Row in which change was 
made
 myText = Range("N" & Target.Row)
'Find that value in Sheet1, Column A
  With Sheets(1).Range("A:A")
       Sheets(3).Range("K:K")     'I made change here
   Set c = .Find(myText, LookIn:=xlValues)
'If found, update Sheet1 with values from Sheet2
     If Not c Is Nothing Then
       Sheets(1).Range("B" & c.Row) = Sheets(2).Range("B" & 
Target.Row)
       Sheets(1).Range("C" & c.Row) = Sheets(2).Range("I" & 
Target.Row)
       Sheets(1).Range("G" & c.Row) = Sheets(2).Range("G" & 
Target.Row)
       Sheets(1).Range("L" & c.Row) = Sheets(2).Range("F" & 
Target.Row)
       Sheets(1).Range("J" & c.Row) = Sheets(2).Range("E" & 
Target.Row)
       Sheets(3).Range("B" & c.Row) = Sheets(2).Range("B" & 
Target.Row)     'Changed line
       End If
  End With
End If
End Sub



Report •

#8
March 11, 2010 at 05:17:47
Your On Error Resume Next line is masking the error that is causing your problem. I'm not sure why you added the On Error statement.

As I pointed out in my earlier response, this section of code will cause VBA to throw up an error:

With Sheets(1).Range("A:A")
       Sheets(3).Range("K:K")    

As per the VBA Help files:

With Statement
     Executes a series of statements on a single object 
     or a user-defined type.

"With" can only be used on one object at a time. In the case of your code, you are telling VBA to work "with" Sheets(1).Range("A:A"). Then you have an extra line [Sheets(3).Range("K:K")] that does nothing but cause an error, which you then mask with the On Error statement.

In other words, the code is never looking at Sheets(3).Range("K:K") but not stopping because you told it to Resume Next if it finds any error, even an illegal line of code.

You have to work "with" each object (Sheet.Range) separately. In Response #4, when I said "By replicating the code to search each sheet, one at a time..." I meant something like this:


  With Sheets(1).Range("A:A")
   'Do stuff with Sheet1
    Set c = .Find etc.
  End With
  
 With Sheets(3).Range("K:K")
   'Do stuff with Sheet3
    Set c = .Find etc.
  End With

 With Sheets(4).Range("M:M")
   'Do stuff with Sheet4
    Set c = .Find etc.
  End With



Report •

#9
March 11, 2010 at 20:29:52
Thanks I learned and i got it. Your Response No.8 is wonderful and helps a lot.

Report •

#10
March 12, 2010 at 01:36:21
Glad to have been of assistance.

Report •


Ask Question