Search key:value pairs, search text file, search & replace text

N

novapaddy

New Member
Joined
Jan 6, 2018
Messages
3
Hello! I mod old strategy games for a hobby. The macros I try to get working are used for modding game files for Paradox's Darkest Hour game.

I have a working macro which I now would like to extend to complete the next job. To explain how it works: Columns A:B are key:value pairs, Column D contains province IDs. The macro works down column D, finds a match in column A, returns the corresponding value in B, and prints the result in another cell.

Here is the macro:
Sub Macro3()
Dim myLastRow As Variant
Dim myRow As Integer
Dim myFind As String
Dim myMatch As String
Dim myReplace As Range
Dim cell As Range
Dim myColumn As Range
Dim arr As Range
Dim i As Integer


i = 2
Sheets("Test").Select
Set arr = Range("D2:D30")
For Each myReplace In arr
'MsgBox "" & myReplace


Range("A2:A30").Select
Set myColumn = Cells.Find(myReplace.Value, After:=Range("A1"), LookAt:=xlWhole, SearchOrder:=xlByColumns)
'MsgBox "" & myColumn
'MsgBox "" & myColumn.Address
myFind = myColumn.Offset(0, 1).Value


If myFind <> "" Then
'MsgBox "" & myFind
Cells(i, 7).Value = myFind
i = i + 1
End If


Next
End Sub

PROBLEM: I have a text file from the game. I want to search the file for stuff like:
addcore which = 802
province = 836
value = 746

ALL of these numbers are province IDs. You can see I would need to search for "which = 802" on one pass, then the next line and so on until done.

So, using my previous macro, I want to search the text file, find "which = 802" take 802, search my A column for it, find it, take the value in column B, and then replace the value back into the text file, overwriting 802 with the new province ID.

My macro does the searching of the key:value list. The part I need is how to do the text editing part. Can you help please?

Here is the excel file:
[TABLE="width: 320"]
<tbody>[TR]
[TD="class: xl63, width: 64"]Id Old[/TD]
[TD="class: xl63, width: 64"]ID New[/TD]
[TD="class: xl63, width: 64"][/TD]
[TD="class: xl63, width: 64"][/TD]
[TD="class: xl63, width: 64"][/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]0[/TD]
[TD="class: xl63, align: right"]0[/TD]
[TD][/TD]
[TD="class: xl63, align: right"]25[/TD]
[TD="class: xl64, width: 64, align: right"]9[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]1[/TD]
[TD="class: xl63, align: right"]1[/TD]
[TD][/TD]
[TD="class: xl63, align: right"]2[/TD]
[TD="class: xl64, width: 64, align: right"]2[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]2[/TD]
[TD="class: xl63, align: right"]2[/TD]
[TD][/TD]
[TD="class: xl63, align: right"]3[/TD]
[TD="class: xl64, width: 64, align: right"]23[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]3[/TD]
[TD="class: xl63, align: right"]23[/TD]
[TD][/TD]
[TD="class: xl63, align: right"]4[/TD]
[TD="class: xl64, width: 64, align: right"]3050[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]4[/TD]
[TD="class: xl63, align: right"]3050[/TD]
[TD][/TD]
[TD="class: xl63, align: right"]31[/TD]
[TD="class: xl64, width: 64, align: right"]634[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]5[/TD]
[TD="class: xl63, align: right"]24[/TD]
[TD][/TD]
[TD="class: xl63, align: right"]32[/TD]
[TD="class: xl63, align: right"]121[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]6[/TD]
[TD="class: xl63, align: right"]25[/TD]
[TD][/TD]
[TD="class: xl63, align: right"]292[/TD]
[TD="class: xl63, align: right"]413[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]7[/TD]
[TD="class: xl63, align: right"]26[/TD]
[TD][/TD]
[TD="class: xl63, align: right"]78[/TD]
[TD="class: xl63, align: right"]81[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]8[/TD]
[TD="class: xl63, align: right"]33[/TD]
[TD][/TD]
[TD="class: xl63, align: right"]409[/TD]
[TD="class: xl63, align: right"]1023[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]9[/TD]
[TD="class: xl63, align: right"]34[/TD]
[TD][/TD]
[TD="class: xl63, align: right"]254[/TD]
[TD="class: xl63, align: right"]1137[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]10[/TD]
[TD="class: xl63, align: right"]40[/TD]
[TD][/TD]
[TD="class: xl63, align: right"]1353[/TD]
[TD="class: xl63, align: right"]2229[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]11[/TD]
[TD="class: xl63, align: right"]41[/TD]
[TD][/TD]
[TD="class: xl63, align: right"]195[/TD]
[TD="class: xl63, align: right"]686[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]12[/TD]
[TD="class: xl63, align: right"]42[/TD]
[TD][/TD]
[TD="class: xl63, align: right"]103[/TD]
[TD="class: xl63, align: right"]673[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"][/TD]
[TD="class: xl63, align: right"][/TD]
[TD][/TD]
[TD="class: xl63, align: right"][/TD]
[TD="class: xl63, align: right"][/TD]
[/TR]
</tbody>[/TABLE]
Here is the text file:


#
# HoI - Hungarian Events
#
# written by Henrik Fåhraeus
#
### 2022006 HUN The End of Czechoslovakia - Ruthenia ceded to Hungary
### 2022007 HUN The End of Czechoslovakia - Slovakia given to Hungary
### 2022008 HUN The End of Czechoslovakia - Czechoslovakia fight back!
### 2022009 HUN The ViennaVienna Dictate - Claim Transylvania with German blessing
### 2022010 HUN The Vienna Dictate - Germans rule in Romanian favour
### 2022011 HUN The Vienna Dictate - Romania folds under German duress
### 2022012 HUN The Vienna Dictate - Romania refuses the demands!
### 2022013 HUN The Molotov-Ribbentrop Pact - Eastern Poland to Soviets
### 2022014 HUN An interesting opportunity
### 2022015 HUN The Romanian Surrender
### 2022016 HUN The Hungarian Defeat


#########################################################################
# The End of Czechoslovakia - Ruthenia ceded to Hungary
#########################################################################
event = {
id = 2022006
random = no
country = HUN


# Triggered by SLO 2057001


name = "EVT_2500_NAME"
desc = "EVT_2500_DESC"
style = 2
picture = "news_paper"


action_a = {
name = ACTION_NAME_OK
command = { type = dissent value = -5 }
command = { type = relation which = SLO value = 5 }
command = { type = relation which = GER value = 5 }
}
}
#########################################################################
# The End of Czechoslovakia - Slovakia given to Hungary
#########################################################################
event = {
id = 2022007
random = no
country = HUN


# Triggered by CZE 2025003


name = "EVT_2501_NAME"
desc = "EVT_2501_DESC"
style = 2
picture = "news_paper"


action_a = {
name = ACTION_NAME_OK
command = { type = dissent value = -20 }
command = { type = relation which = GER value = 10 }
command = { type = addcore which = 801 } # Bratislava - Czechoslovakia
command = { type = addcore which = 802 } # Banská Bystrica - Czechoslovakia
command = { type = addcore which = 813 } # Zilina - Czechoslovakia
command = { type = addcore which = 804 } # Kosice - Czechoslovakia
command = { type = addcore which = 805 } # Chust - Czechoslovakia
### added by ewphoenix123 ###
command = { type = addcore which = 807 } # Komárno - Czechoslovakia
command = { type = addcore which = 804 } # Kosice - Czechoslovakia
command = { type = addcore which = 808 } # Uzhorod - Czechoslovakia
command = { type = addcore which = 806 } # Presov - Czechoslovakia
}
}
#########################################################################
# The End of Czechoslovakia - Czecho-Slovakia fight back!
#########################################################################
event = {
id = 2022008
random = no
country = HUN

# Triggered by CZE 2025003

name = "EVT_2502_NAME"
desc = "EVT_2502_DESC"
style = 2
picture = "news_paper"


action_a = {
name = ACTION_NAME_OK
command = { type = dissent value = 10 }
command = { type = relation which = CZE value = -15 }
}
}
#########################################################################
# The Vienna Diktat - Claim Transylvania with German blessing
#########################################################################
event = {
id = 2022009
random = no
country = HUN

trigger = {
exists = GER
exists = ROM
control = { province = 836 data = ROM } #Oradea
control = { province = 832 data = ROM } #Cluj
control = { province = 844 data = ROM } #Miercurea Ciuc
control = { province = 837 data = ROM } #Bistrita
government = fascist
NOT = {
war = { country = HUN country = ROM }
puppet = { country = HUN country = SOV }
puppet = { country = ROM country = SOV }
OR = {
control = { province = 834 data = -1 } #Timiosara
control = { province = 835 data = -1 } #Arad
control = { province = 836 data = -1 } #Oradea
}
}
}

name = "EVT_2503_NAME"
desc = "EVT_2503_DESC"
style = 2
picture = "news_paper"

date = { day = 4 month = september year = 1940 }


action_a = {
name = "ACTIONNAME2503A" # Press the issue
ai_chance = 98
command = { type = relation which = ROM value = -10 }
command = { type = trigger which = 2003031 } # GER
}
action_b = {
name = "ACTIONNAME2503B" # Refrain
ai_chance = 2
command = { type = domestic which = interventionism value = -1 }
command = { type = relation which = ROM value = 10 }
}
}
#########################################################################
# The Vienna Diktat - Germans rule in Romanian favor
#########################################################################
event = {
id = 2022010
random = no
country = HUN


# Triggered by GER 2003031


name = "EVT_2504_NAME"
desc = "EVT_2504_DESC"
style = 2
picture = "news_paper"


action_a = {
name = ACTION_NAME_OK
command = { type = dissent value = 5 }
}
}
#########################################################################
# The Vienna Diktat - Romania folds under German duress
#########################################################################
event = {
id = 2022011
random = no
country = HUN

# Triggered by ROM 2023004

name = "EVT_2505_NAME"
desc = "EVT_2505_DESC"
style = 2
picture = "news_paper"


action_a = {
name = ACTION_NAME_OK
command = { type = dissent value = -10 }
command = { type = relation which = GER value = 5 }
}
}
#########################################################################
# The Vienna Diktat - Romania refuses the demands!
#########################################################################
event = {
id = 2022012
random = no
country = HUN

# Triggered by ROM 2023004

name = "EVT_2506_NAME"
desc = "EVT_2506_DESC"
style = 2
picture = "news_paper"


action_a = {
name = ACTION_NAME_OK
command = { type = dissent value = 5 }
command = { type = relation which = ROM value = -10 }
command = { type = trigger which = 2023006 }
#command = { type = alliance which = GER when = 1 } # Alliance with Germany if not already
}
}


#########################################################################################
# M-R Pact effects copy to ensure HUN doesn't keep any conquests were promised to SOV


event = {
id = 2022013
random = no
country = HUN


# Triggered by German event 2003029


name = "EVT_2018_NAME"
desc = "EVT_2018_DESC"
style = 2
picture = "news_paper"


action_a = {
name = "ACTIONNAME2018A" # Honor the Pact
command = { type = secedeprovince which = SOV value = 748 } # Przemysl - Poland#
command = { type = secedeprovince which = SOV value = 719 } # Bialystok - Poland#
command = { type = secedeprovince which = SOV value = 721 } # Lomza - Poland#
command = { type = secedeprovince which = SOV value = 751 } # Stryj - Poland#
command = { type = secedeprovince which = SOV value = 746 } # Lwów - Poland#
command = { type = secedeprovince which = SOV value = 725 } # Równe - Poland#
command = { type = secedeprovince which = SOV value = 726 } # Brzesc Litewski - Poland#
command = { type = secedeprovince which = SOV value = 728 } # Luck - Poland#
command = { type = secedeprovince which = SOV value = 747 } # Stanislawów - Poland#
command = { type = secedeprovince which = SOV value = 729 } # Pinsk - Poland#
command = { type = secedeprovince which = SOV value = 743 } # Tarnopol - Poland#
command = { type = secedeprovince which = SOV value = 738 } # Nowogródek - Poland#
command = { type = secedeprovince which = SOV value = 718 } # Grodno - Poland#
command = { type = secedeprovince which = SOV value = 737 } # Glebokie - Poland#
command = { type = secedeprovince which = SOV value = 713 } # Wilno - Poland#
command = { type = secedeprovince which = SOV value = 730 } # Sarny - Poland#
command = { type = secedeprovince which = SOV value = 735 } # Molodeczno - Poland#
command = { type = secedeprovince which = SOV value = 736 } # Slonim - Poland#
}
}


#########################################################################
# Hungary exploits the situation
#########################################################################
event = {
id = 2022014
random = no
country = HUN


# Triggered by BUL 2020002


name = "EVT_2713_NAME"
desc = "EVT_2713_DESC"
style = 2
picture = "news_paper"


trigger = {
event = 2020002
war = { country = BUL country = ROM }
random = 40
}


date = { day = 2 month = january year = 1936 }
offset = 7 # Check for trigger conditions day
deathdate = { day = 29 month = december year = 1963 }


action_a = {
name = "EVT_2713_ACTA" # Hungary gang-bangs Romania
ai_chance = 75
command = { type = leave_alliance when = 1 }


command = { type = end_guarantee which = SOV where = HUN }
command = { type = end_guarantee which = GER where = HUN }
command = { type = domestic which = interventionism value = 1 }
command = { type = war which = ROM }
#command = { type = dissent value = -10 }
command = { type = sleepevent which = 2023011 }
command = { type = sleepevent which = 2020005 }
command = { type = sleepevent which = 2020007 }
command = { type = sleepevent which = 2023013 }
command = { type = trigger which = 2020004 }
}
action_b = {
name = "EVT_2713_ACTB" # Hungary doesn't get involved
ai_chance = 25
command = { type = non_aggression which = HUN where = ROM }
command = { type = sleepevent which = 2020004 }
command = { type = sleepevent which = 2023010 }
command = { type = sleepevent which = 2023012 }
command = { type = sleepevent which = 2020006 }
command = { type = sleepevent which = 2022015 }
command = { type = sleepevent which = 2020008 }
command = { type = sleepevent which = 2023014 }
command = { type = sleepevent which = 2022016 }
command = { type = sleepevent which = 2023016 }
}
}


#########################################################################
# Hungary Victorious - Hungary involved
#########################################################################
event = {
id = 2022015
random = no
country = HUN


# Triggered by ROM 2023012A


name = "EVT_2716_NAME"
desc = "EVT_2720_DESC"
style = 2
picture = "news_paper"


action_a = {
name = "ACTION_NAME_HURRAH" # Bulgaria has won
command = { type = addcore which = 834 } # Timisoara - Romania
command = { type = addcore which = 835 } # Arad - Romania
command = { type = addcore which = 847 } # Turda - Romania
command = { type = addcore which = 832 } # Cluj - Romania
command = { type = addcore which = 831 } # Alba Iulia - Romania
command = { type = addcore which = 833 } # Sibiu - Romania
command = { type = addcore which = 837 } # Bistrita - Romania
command = { type = addcore which = 844 } # Miercurea Ciuc - Romania
command = { type = addcore which = 840 } # Resita - Romania
command = { type = addcore which = 836 } # Oradea - Romania
command = { type = addcore which = 929 } # Petrovgrad - Balkan
command = { type = dissent value = -20 }
}
}


#########################################################################
# Hungary Defeated - Hungary involved
#########################################################################
event = {
id = 2022016
random = no
country = HUN


name = "EVT_2725_NAME"
desc = "EVT_2725_DESC"
style = 2
picture = "news_paper"


trigger = {
event = 2022014
control = { province = 284 data = ROM } # Budapest
war = { country = HUN country = ROM }
NOT = {
alliance = { country = BUL country = GER }
alliance = { country = ROM country = GER }
alliance = { country = HUN country = GER }
}
}


date = { day = 2 month = january year = 1936 }
offset = 1 # Check for trigger conditions day
deathdate = { day = 29 month = december year = 1963 }


action_a = {
name = "EVT_2725_ACTA" # Magyars are unlucky
command = { type = leave_alliance when = 1 }
command = { type = peace which = ROM value = 1 }
command = { type = secedeprovince which = ROM value = 837 } # Bistrita - Romania
command = { type = secedeprovince which = ROM value = 832 } # Cluj - Romania
command = { type = secedeprovince which = ROM value = 844 } # Miercurea Ciuc - Romania
command = { type = secedeprovince which = ROM value = 836 } # Oradea - Romania
#command = { type = secedeprovince which = ROM value = 824 } # Debrecen - Hungary
#command = { type = secedeprovince which = ROM value = 825 } # Békéscsaba - Hungary
command = { type = removecore which = 832 } # Cluj - Romania
command = { type = removecore which = 844 } # Miercurea Ciuc - Romania
command = { type = removecore which = 836 } # Oradea - Romania
command = { type = removecore which = 837 } # Bistrita - Romania
command = { type = dissent value = 20 }
command = { type = trigger which = 2023015 } #Romania victorious over Hungary
}
}

I could not find a way to just attach the files.
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
Sort by date Sort by votes
MORE: I meant to add:
There are more than one type of possible form of finding a match to the key, they take different forms, like this:
addcore which = 801
province = 836
value = 748

So for an absolute GENIUS solution, the match could be this OR this OR this. THANKS

It is just the number I am wanting to search and replace. THANKS
 
Last edited by a moderator:
Upvote 0
Re: This problem will certainly test your capabilities! Key:value pairs and a text file

Could you present a mini-demonstration of what you need?
 
Upvote 0
Re: This problem will certainly test your capabilities! Key:value pairs and a text file

István Hirsch said:
Could you present a mini-demonstration of what you need?
Click to expand...
Thanks for your reply. The text file will be something like this sample. You can see how the numbers are placed in the text.

event = {
id = 2022007
random = no
country = HUN
name = "EVT_2501_NAME"
desc = "EVT_2501_DESC"
style = 2
picture = "news_paper"


action_a = {
name = ACTION_NAME_OK
command = { type = dissent value = -20 }
command = { type = relation which = GER value = 10 }
command = { type = addcore which = 801 } # Bratislava - Czechoslovakia
control = { province = 836 data = ROM } #Oradea
command = { type = addcore which = 802 } # Banská Bystrica - Czechoslovakia
command = { type = addcore which = 813 } # Zilina - Czechoslovakia
command = { type = secedeprovince which = SOV value = 748 } # Przemysl - Poland#
command = { type = addcore which = 804 } # Kosice - Czechoslovakia
command = { type = addcore which = 805 } # Chust - Czechoslovakia
}
}

The key:value pairs in the excel worksheet are just a series of numbers. I have already done an excel macro which did the finding of the key, picked up the match, and printed the result in another column:

Here is my macro:
Sub Macro3()
Dim myLastRow As Variant
Dim myRow As Integer
Dim myFind As String
Dim myMatch As String
Dim myReplace As Range
Dim cell As Range
Dim myColumn As Range
Dim arr As Range
Dim i As Integer


i = 2
Sheets("Test").Select
Set arr = Range("D2:D30")
For Each myReplace In arr
'MsgBox "" & myReplace


Range("A2:A30").Select
Set myColumn = Cells.Find(myReplace.Value, After:=Range("A1"), LookAt:=xlWhole, SearchOrder:=xlByColumns)
'MsgBox "" & myColumn
'MsgBox "" & myColumn.Address
myFind = myColumn.Offset(0, 1).Value


If myFind <> "" Then
'MsgBox "" & myFind
Cells(i, 7).Value = myFind
i = i + 1
End If


Next
End Sub
 
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,224,823
Messages
6,181,176
Members
453,021
Latest member
Justyna P

Share this page

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
Back
Top