Updating bookmarks in a range with strings, dependent on cell values in a range

I am trying to print information to a series of bookmarks in a word document. The information I want to print is dependent on the values held in cells in a range.

For example:

I have 5 bookmarks in a word document. I have 5 cells in my excel sheet, A1 to A5.
The cell A1 corresponds to the first bookmark, A2 to the second, so on and so forth.
The cell values are between 1 and 20. For the value in cell A1, if it is between 1 and 3 I want to return "Very Low". If the cell A1 has a value between 3 and 5, I want to return "Low", etc. etc. This should go on until a match is found.
When a match is found, a string is printed at the corresponding bookmark for that cell and we move onto the next cell and the next bookmark.

Currently, I have only managed to alter the cell contents themselves and then print to a bookmark. I do not want to alter the cell contents.

Is there a way to print what returns from the If/ ElseIf straight to a bookmark? How would this work for a range of bookmarks?

This is what I have so far:

Dim rCell As Range, rArea As Range


Set rArea = ws.Range("A1:A5")
 For Each rCell In rArea.Cells
        If rCell.Value <= 3 Then
            rCell.Value = rCell.Value & " (Very Low)"

        ElseIf rCell.Value >= 3 And rCell.Value <= 5 Then
            rCell.Value = rCell.Value & " (Low)"
    
        ElseIf rCell.Value >= 5 And rCell.Value <= 10 Then
            rCell.Value = rCell.Value & " (Target)"
   
        ElseIf rCell.Value >= 10 and rCell.Value <= 20 Then
            rCell.Value = rCell.Value & " (Excess)"
        End If
     Next rCell

' Print to bookmarks
With wDoc
.Bookmarks("p1").Range = ws.Range("A1").Value
.Bookmarks("p2").Range = ws.Range("A2").Value
.Bookmarks("p3").Range = ws.Range("A3").Value
.Bookmarks("p4").Range = ws.Range("A4").Value
.Bookmarks("p5").Range = ws.Range("A5").Value
End With

If anyone understood that and knows how to help... thank you so much

CodePudding user response：

You can introduce a helper function that calculates the desired text based on the cell value and then print the returned value to the bookmark.

Keep in mind the numbers in your example overlap, for example 3 falls in both cases (very low & low) so update them as required.

A helper method that returns a string based on the cell value.

Private Function ToGrade(ByVal r As Range) As String
    Select Case r.Value
        Case 1 To 2:    ToGrade = "Very Low"
        Case 3 To 4:    ToGrade = "Low"
        Case 5 To 10:   ToGrade = "Target"
        Case 11 To 20:  ToGrade = "Excess"
        Case Else:      ToGrade = "Unknown"
    End Select
End Function

Since both ranges run continuously, you can loop and concatenate their names:

Dim i As Integer

For i = 1 To 5 'or 20
    wDoc.Bookmarks("p" & i).Range.Text = ToGrade(ws.Range("A" & i))
Next i

CodePudding user response：

For example:

Dim r As Long
For r = 1 To 5
  With ws.Range("A" & r)
    Select Case .Value
      Case Is >= 10
        Call UpdateBookmark(wdDoc, "p" & r, .Value & " (Excess)")
      Case Is >= 5
        Call UpdateBookmark(wdDoc, "p" & r, .Value & " (Target)")
      Case Is >= 3
       Call UpdateBookmark(wdDoc, "p" & r, .Value & " (Low)")
      Case Is < 3
       Call UpdateBookmark(wdDoc, "p" & r, .Value & " (Very Low)")
    End Select
  End With
Next

coupled with:

Sub UpdateBookmark(wdDoc As Word.Document, StrBkMk As String, StrTxt As String)
Dim BkMkRng As Word.Range
With wdDoc
  If .Bookmarks.Exists(StrBkMk) Then
    Set BkMkRng = .Bookmarks(StrBkMk).Range
    BkMkRng.Text = StrTxt
    .Bookmarks.Add StrBkMk, BkMkRng
    MsgBox StrBkMk & " bookmark updated with " & StrTxt, vbInformation
  Else
    MsgBox StrBkMk & " bookmark NOT found!" & StrTxt, vbExclamation
  End If
End With
Set BkMkRng = Nothing
End Sub

If you're using late binding, change wdDoc As Word.Document to wdDoc As Object and change BkMkRng As Word.Range to BkMkRng As Object