I am new to VB I need to compare the address on both columns.
If both addresses match check whether both data match, if not output both source file and dump file address and data to a text file.
If an address in column A is not found in column C, output source file address and data to a text file.
If an address in column C is not found in column A, output dump file address and data to a text file.
Hope someone can help me. Thanks!
Source File Dump File
a b c d
1 address data address data
2 s100 a s010 x
3 S010 x s020 b
4 S030 y S030 y
5 s040 z S040 d
One Issue
If the addresses match, I think it doesnt check whether the data is the same at both the addresses. For example, source file has address 's040' with data 'z' but dump file has address 's040' with data 'd'
Time Issue
It took very long as there are ard 900M iterations. Is it better to to remove the duplicates first then run this search? I tried to use excel's remove duplicates feature but it only work for one column. It takes ard 25mins for the whole cycle.
Combining Data if Consecutive addresses are unique in a list:
If there is a chunk of unique addresses in one column, I need to find the start address of the first unique address and the last unique address and output only those lines which are not all FF's like this:
'If all the data are FF's output like this
File: dump.s19
0x006180 – 0x007E8F
[Result] OK
'Here certain lines are all FF's which are not displayed here, only Non FF's lines need to printed as follows.
File: dump.s19
0x007EB0 – 0x00FFFF
S224007FF0FFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF4247494EFFFFFFFFFFFFFFFF0000FFFF66
S224008010FFFFFFFF01019D160825A100100201FFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFCA
S224008050302D4100E0FFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFA8
S224008070FFFFFFFFFFFFFFFFFFFFA3BF454E442EFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF9E
Result: NOK
I am not sure how to integrate this additional logic into my code.
'Set the address you're trying to find
fa = Range(sf & cr).Value
fa_data = Range(Chr(Asc(sf) + 1) & cr).Value
Debug.Print "fa" & fa
'Find it
Set targetcell = Range(si & 3 & ":" & si & lr_2).Find(What:=fa, LookIn:=xlValues, _
SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=True)
'If Nothing is returned it is not found
If targetcell Is Nothing Then
'Write your search cell and it's ajacent to your file.
If l = 1 Then
startadd = Range(sf & cr).Value
If startadd <> "FFFFFF" Then
Dec_startadd = Val("&H" & startadd & "&H")
lgth = Len(Range(Chr(Asc(sf) + 1) & cr)) - 2
lgth = lgth / 2 - 1
endadd = Hex(Dec_startadd + lgth)
endadd = Right("000000" & endadd, 6)
Print #fn, "File:" & orig_filename
Print #fn, "0x" & startadd & " - 0x" & endadd
Print #fn, Range(Chr(Asc(sf) - 1) & cr).Value & Range(sf & cr).Value & Range(Chr(Asc(sf) + 1) & cr).Value
Print #fn, "Result: NOK"
Print #fn,
Print #fn,
I whipped this up for the fun of it:
'Get a the next available file number
fn = FreeFile
'Open your file ready for writing.
Open "your full path and file name" For Output As #fn
'Set the First row to search from.
fr = 2
'Find the last row.
lr = ActiveCell.SpecialCells(xlLastCell).Row
'Set the column for the value that you are searching for.
sf = "A"
'Set the column for that you are searching in.
si = "C"
'You want to search two columns
For l = 1 To 2
'Loop from first row to the last row.
For cr = fr To lr
'Set the address you're trying to find
fa = Range(sf & cr).Value
'Find it
Set targetcell = Range(si & fr & ":" & si & lr).Find(What:=fa, LookIn:=xlValues, _
SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
'If Nothing is returned it is not found
If targetcell Is Nothing Then
'Write your search cell and it's ajacent to your file.
Write #fn, Range(sf & cr).Value & "," & Range(Chr(Asc(sf) + 1) & cr).Value
End If
'I always put a DoEvents in a loop; just in case you need to break out of it.
DoEvents
Next
'Now you've done one column swap them over and do it again.
sf = "C"
si = "A"
Next
'It's done.
Close #fn