Solved: lookup corresponding value in word-table

[s?ren]

Hi there!

I need to find a value in a 2-column table. The value depends on the value in column 1. I.e.: When I type "10" I need the macro to run down the rows in column 1 and find the value "10" and return the corresponding "row"-value in column 2.
I know how to do this in Excel, but I can not use excel in this case.
Is this possible in Word 2002 and if so, how?

regards

S?ren

[Bob Phillips]

ActiveDocument.Tables(1).Select
Selection.Find.ClearFormatting
Selection.Find.Replacement.ClearFormatting
With Selection.Find
.Text = "10"
.Forward = True
.Wrap = wdFindStop
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
End With
Selection.Find.Execute
Selection.MoveRight Unit:=wdCell
MsgBox Selection.Text

[TonyJollans]

Word isn't like Excel and doesn't do this kind of thing well.

Using Find for this is one of those rare occasions where the Selection must be used because a Table Column can not be a Range. There are a couple of problems with it, however:

Find will only find a string within one of the cells being searched, not necessarily the whole cell contents so if your cells are all numeric and in order you will be alright; if not you will need to do a further check and possibly set the find in a loop.

The other problem is that there is no logic for a not found condition - again this may not matter in particular circumstances but more generally you should change


Selection.Find.Execute
Selection.MoveRight Unit:=wdCell
MsgBox Selection.Text


to


If Selection.Find.Execute Then
Selection.MoveRight Unit:=wdCell
MsgBox Selection.Text
End If

[s?ren]

Thanks!

I wasn't clear about my specific need the first time, so:
Here is what I need to do:
I have a list of numbers (1-100) with corresponding values, and I
I need to be able to enter any of these numbers and have the corresponding value returned in a table for further calculation.

There is only one number in each cell, (no text). So that part will not be a problem.

S?ren

[Bob Phillips]

Juat add an inputbos



ans=Inputbox("Supply value")



and then use the variable ans in the code given.

[TonyJollans]

If you have the numbers 1 to 100 in order in a table then the row number should get you straight where you want to be - no need to bother searching.

[fumei]

Yes, I am not understanding the problem.

Col 1
1
2
3
......
100

I have a list of numbers (1-100) with corresponding values, and I
I need to be able to enter any of these numbers and have the corresponding value returned in a table for further calculation.What exactly do you mean by "corresponding value"?

[fumei]

I would also like to add that while true you can not make a Range of a column, you CAN make an object of a column. Therefore to do this you do NOT need to use Selection.
Dim oTable As Table
Dim oCol As Column
Dim oCell As Cell

Set oTable = ActiveDocument.Tables(1)
Set oCol = oTable.Columns(1)
For Each oCell In oCol.Cells
' need to strip off end-of-cell marker
If Left(oCell.Range.Text, Len(oCell.Range.Text) - 2) _
= "10" Then
' uses oCell row index and 2 for Column 2 as
' cell index)
oTable.Cell(oCell.Row.Index, 2).Range.Text _
= "whatever"
End If
NextThis will find the row in Column 1 with "10" in it and put "whatever" into Column 2 of the same row. I used "whatever", as I am confused about what exactly you are doing.

It sounds like you are looking for a row with "10" in it, and putting "10" in Column 2. Which seems odd. Oh, way a second.

You want to to put the row value in Column 2. OK, here you go. I just tested this. If "10" is in Row 3, then Row 3, Col2 will be "3".Dim oTable As Table
Dim oCol As Column
Dim oCell As Cell

Set oTable = ActiveDocument.Tables(1)
Set oCol = oTable.Columns(1)
For Each oCell In oCol.Cells
If Left(oCell.Range.Text, Len(oCell.Range.Text) - 2) _
= "10" Then
oTable.Cell(oCell.Row.Index, 2).Range.Text _
= oCell.Row.Index
End If
NextNo using Selection. No using Find. No movement of any kind actually.

[fumei]

Oh, just in case...Left(oCell.Range.Text, Len(oCell.Range.Text) - 2)is used because you need to strip off the end-of-cell marker to get the text value in the cell when using the range.

[fumei]

Of course, if you have merged and funny cell/column structure this could very well get tossed out the window!

[TonyJollans]

You are, of course, correct that you can avoid using the Selection by using a different technique - but it is sloooowwww compared to the Selection.Find method.

[fumei]

RE: slooooowwwww

True. This has to be part of any consideration of routes to take. Although I just did a test with a table of 360 rows, comparing using Selection.Find and using the column as an object. I did not use a really fine tuned timing process(just a Start and End use of Now, formatted for seconds), but there was no difference at all. Zero. So I am not sure that "slooooowwww" is all that relevant in this case.

Any speedier use of Find may very well be eaten up by also using Selection to move between cells, and type text.

The other advantage, though, of not using Selection is that you do not have to have the Selection in the table. You could expand the procedure to process all tables in a document.

It depends on what the needs and requirements are.

BTW: for the OP, if you do use the Selectiion method, and I am correct you are trying to get the row value into Column 2, then you will use:Selection.Find.Execute
Selection.MoveRight Unit:=wdCell
Selection.TypeText Text:=Selection.Cells(1).Row.IndexThe examples given by Tony and xld were displaying only a message box.

[fumei]

I did a test where I searched up and down the table with multiple search items - that is, looking for specifically rows 44, 87, 102, 13, 90, 311, 217 (in THAT order) - and I still can not get a detectable difference.

[s?ren]

Re. use of columns
Column 1 contains no. 1-100 (amount of years)
Column 2 contains corresponding numbers like so:

Col 1 Col 2
1 1.0
2 2.0
3 3.1
4 4.2
5 5.3
6 6.4
7 7.6
..
20 26.7
etc.

I need to fill in a given amount of years in a field and have the macro search column 1 and return the corresponding number from column 2.
So if I put "7" into the field, the macro needs to run down column 1, and return the corresponding number "7.6" in another field or cell.
I hope this answers some of your questions.
S?ren

[Bob Phillips]

Dim ans
ans = InputBox("Supply value")
MsgBox ActiveDocument.Tables(1).cell(ans, 2).Range.Text

[TonyJollans]

Hi Gerry,

I was going to post something similar to your code after I saw Bob's first post but a quick test - just using the single example - showed it to take almost three times as long so I decided not to.

I will try something a bit more representative later but a thought occurs to me. I have a newish computer with a Core2 Duo processor and I wonder if the VBA and the screen action because of Selection maybe use a processor each.

[fumei]

Now THAT would be interesting, if true. How would you find that out?

Also, three times as long???? Why am I not getting those kind of numbers in my tests?

[s?ren]

I have managed to create a macro that works.
The document needs to contain 5 commandbuttons and 3 tables
table 1: 2 columns, 5 rows (contains the answers entered)
table 2: 6 columns, 1 row (contains the returned text from table 3)
table 3: 2 columns, 100 rows (contains my array of years and corresponding factors)

Private Sub CommandButton1_Click()
'***** Grundtakst *****

Dim Message, Title, MyValue1
Message = "Indtast grundtakst jvf. takstregulativ"
Title = "Grundtakst"
MyValue1 = InputBox(Message, Title)

ActiveDocument.Tables(1).Rows(1).Cells(2).Range = MyValue1

CommandButton2_Click

End Sub

Private Sub CommandButton2_Click()
'***** Reguleringstakst *****

Dim Message, Title, Default, MyValue2
Message = "Indtast ?rlig reguleringstakst pr. 1/4"
Title = "Reguleringstakst"
Default = "31,93"
MyValue2 = InputBox(Message, Title, Default)

ActiveDocument.Tables(1).Rows(2).Cells(2).Range = MyValue2

CommandButton3_Click

End Sub

Private Sub CommandButton3_Click()
'***** Aktuelt antal ?r *****

Dim Title, MyValue3
Title = "Aktuelt antal ?r"

ActiveDocument.Tables(3).Columns(1).Select
Selection.Find.ClearFormatting
Selection.Find.Replacement.ClearFormatting

With Selection.Find
.Text = InputBox("indtast ?nsket antal ?r", Title)

MyValue3 = .Text
ActiveDocument.Tables(1).Rows(3).Cells(2).Range = MyValue3

.Forward = True
.Wrap = wdFindStop
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
End With

If Selection.Find.Execute Then
Selection.MoveRight (wdCell)
Selection.Copy
ActiveDocument.Tables(2).Rows(1).Cells(2).Range _
.Paste
End If

CommandButton4_Click

End Sub

Private Sub CommandButton4_Click()
'***** Restv?rdi, eksisterende aftale *****

Dim Title, MyValue4
Title = "Restv?rdi, eksisterende aftale"

ActiveDocument.Tables(3).Columns(1).Select
Selection.Find.ClearFormatting
Selection.Find.Replacement.ClearFormatting

With Selection.Find
.Text = InputBox("Indtast resttid af eksisterende aftale", Title)

MyValue4 = .Text
ActiveDocument.Tables(1).Rows(4).Cells(2).Range = MyValue4

.Forward = True
.Wrap = wdFindStop
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
End With

If Selection.Find.Execute Then
Selection.MoveRight (wdCell)
Selection.Copy
ActiveDocument.Tables(2).Rows(1).Cells(4).Range _
.Paste
End If

CommandButton5_Click

End Sub

Private Sub CommandButton5_Click()
'***** Fredningsperiode *****

Dim Message, Title, Default, MyValue5
Message = "Indtast fredningsperiode, aktuelt gravsted 10 ?r hhv. 25 ?r"
Title = "Fredningsperiode"
Default = "10"
MyValue5 = InputBox(Message, Title, Default)

ActiveDocument.Tables(1).Rows(5).Cells(2).Range = MyValue5

MsgBox ("Alle n?dvendige oplysninger er indtastet. Beregning udf?res.")

End Sub

[s?ren]

I had a few problems in the process though.

I got some funny numbers, when I pasted the text into table 1.
I got both the found numbers as well as some ghost-numbers that came out of the blue.
I searched the forum for answers to this problem but didn't really find anything that matched my problem - or so I thought. See below*
This morning I could no longer open my document - got this message: document cannot be opened - too many corrections (translation not to good, but this is the essence of the message.)
So I had to create a new document from scratch.
Here I found the problem (I think). When I created the first document, I first put in 4 tables, but realized I only needed 3. So I deleted 1.
This time I created the document as I needed, without any extra tables.
The macro code is the same, but this time it worked fine.

I don't quite understand the speed-issues you guys discuss. It doesnt seem slow to me at all.

Anyway

Thanks for the help and pointers

best regards

S?ren