PDA

View Full Version : how to search the next value in excel



johan4b
10-06-2016, 03:26 AM
Hi guys ik hope you can help me i am new to VBA. I searched the internet for how to do if .. then otherwise if...

But i can't find the right way to do

What is want is: When cell A3 = 2 then find <<>> in word and paste text. But when A3 is 1 or three i want vba to paste another text in another cell.

i hope you can help me with it.

My is:

Option Explicit

Private Sub CommandButton1_Click()
Dim wApp As Word.Application
Dim myFile
Dim wDoc As Word.Document

myFile = Application.GetOpenFilename("Word Files (*.doc*), *.doc*")

If VarType(myFile) = vbBoolean Then Exit Sub
If Not myFile Like "*.doc*" Then Exit Sub

Set wApp = New Word.Application
wApp.Visible = True
Set wDoc = wApp.Documents.Open(myFile)

If Range("A3") = 2 Then
End If
With wDoc.Application.Selection
.Find.Text = "<<A1>>"
If .Find.Execute Then
.Text = Range("C4")
.EndOf wdWord, wdMove
End If


If Range("A3") = 3 Then
End If
With wDoc.Application.Selection
.Find.Text = "<<A2>>"
If .Find.Execute Then
.Text = Range("C8")
.EndOf wdWord, wdMove

End If


End With
End With
End Sub

mana
10-06-2016, 04:31 AM
Option Explicit

Sub test()
Dim wApp As Word.Application
Dim myFile
Dim wDoc As Word.Document
Dim searchText As String
Dim copyText As String

Select Case Range("A3").Value
Case 2
searchText = "<<A1>>"
copyText = Range("C4").Value
Case 3
searchText = "<<A2>>"
copyText = Range("C8").Value
Case Else
Exit Sub
End Select

myFile = Application.GetOpenFilename("Word Files (*.doc*), *.doc*")
If VarType(myFile) = vbBoolean Then Exit Sub
If Not myFile Like "*.doc*" Then Exit Sub

Set wApp = New Word.Application
wApp.Visible = True
Set wDoc = wApp.Documents.Open(myFile)

With wDoc.Content
.Find.Text = searchText
If .Find.Execute Then
.Text = copyText
End If
End With

End Sub

johan4b
10-06-2016, 06:32 AM
Mana

Thanks a lot this is what i'm looking for your the best

The last thing is that when the value in c4 is copyed it shows also <<A2>>. Is there a way to just show the value in C4 and hide <<A2>>

Thanks a lot

mana
10-06-2016, 07:06 AM
Option Explicit

Sub test2()
Dim wApp As Word.Application
Dim myFile
Dim wDoc As Word.Document
Dim searchText As String
Dim copyText As String
Dim delText As String

Select Case Range("A3").Value
Case 2
searchText = "<<A1>>"
copyText = Range("C4").Value
delText = "<<A2>>"
Case 3
searchText = "<<A2>>"
copyText = Range("C8").Value
delText = "<<A1>>"
Case Else
Exit Sub
End Select

myFile = Application.GetOpenFilename("Word Files (*.doc*), *.doc*")
If VarType(myFile) = vbBoolean Then Exit Sub
If Not myFile Like "*.doc*" Then Exit Sub

Set wApp = New Word.Application
wApp.Visible = True
Set wDoc = wApp.Documents.Open(myFile)

wDoc.Content.Find.Execute FindText:=searchText, ReplaceWith:=copyText
wDoc.Content.Find.Execute FindText:=delText, ReplaceWith:=""

End Sub