PDA

View Full Version : Update the value from B file to A file



Ann_BBO
10-21-2009, 05:15 AM
Hi All,

I have the 2 files. One is A file and one is B file.

A file contains a many “Blocks”. Each Block has a corresponding mapping table and Offsets. Address = “Block + Offset”.

Example in A file:

http://img197.imageshack.us/img197/5946/screenhunter01oct212007.gif

B file has the “Address” and “Changed Value” that I would like to update to A file.

Example in B file:
http://img197.imageshack.us/img197/4417/screenhunter02oct212007.gif

The comparison method is : Checking the “Address” and “Name”. Note that “Name” in B file format has the comma and space between each sentences. If both are identical to address and Name in A file, then update the “Changed value” into A file and cell shading with yellow color on corresponding cell in A file.

Expect Result:
http://img62.imageshack.us/img62/2341/screenhunter03oct212008.gif

Difficulties to me :

In A file, how to combine the address from block and offset so that it can do a comparison. Since Block and table haven’t a constant space or it may have a word between them. My idea is find the “Block” word with Bold features. Then, move download (offset) to the row until we meet the green color cell shading.I hope that someone can give me idea to me. Thanks

p.s. Since the max attached file is one. Therefore, i combine 3 excel files to one.

Thanks&regards,
Ann

Ann_BBO
10-21-2009, 09:15 AM
Anyone has ths idea?? Thanks~~

joms
10-21-2009, 07:25 PM
The comparison method is : Checking the “Address” and “Name”. Note that “Name” in B file format has the comma and space between each sentences. If both are identical to address and Name in A file, then update the “Changed value” into A file and cell shading with yellow color on corresponding cell in A file.

hi Ann, just a question but it might give you an idea to go on..why need to compare "Address" and "Name"?

i suppose the name should be unique and the address also..(i assume this is for some circuit design ).. in your A file in value you have this 0x00d8 and in your B file in the default value 00D8.

So just get some function to get the four characters in A file under value so the 0X will not be included and use it for comparison in B file under Default value..then if it matches you can change the data that you want..

just hope it give you some hint.. :)

Ann_BBO
10-21-2009, 08:01 PM
Hi joms,

Thanks your idea. I need to compare the "Address" and "Name" because the address may be changed. That's why i would like to compare the "address" and "name" for error checking.

By the way, it is quite difficult to start by me since i am quite fresh to use VBA. If someone can provide the example or idea, it is better to me.

Thanks,
Ann

joms
10-22-2009, 10:05 PM
hi Ann, i tried to do but it's really quite hard. As you said that, Since Block and table haven’t a constant space or it may have a word between them..

Like in A file you have this: . mic_vol_set. step and in B file you have this: . mic_vol_set. Step.
It's the same but the capital S will make a difference if you compare it VB will treat it as two different data. But there's a work around to convert all string to lower case then compare.
But again the spaces between words is another issue.

Anyway, i'm just also learning VBA maybe guys around here can help you more. Here's the code i try to compare the data on sheet A and sheet B.


Sub x()
Dim i, x As Integer
Dim str, str_a, str_b As String
'Declare an array
Dim split_str() As String

'get the data on sheets B
str = Sheets("B").Cells(8, 6).Value

'split the data on sheets B for comparison
split_str = Split(str, ",")

'Compare the data on Sheet A and B
For i = 0 To UBound(split_str)
str_b = split_str(i)
str_a = Sheets("A").Cells(9 + x, 4).Value
MsgBox str_b & " " & str_a

If str_b = str_a Then
MsgBox "ok"
Else
MsgBox "not ok"
End If
'increment row to get data
x = x + 1
Next

End Sub