VBA - Help with If statement inside loop

test123ja · Oct 6th 2016

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:

Code

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

Display More

test123ja · Oct 6th 2016

Re: Help with if or loop

Hi I hope someone can help me

I have created a code that can realise this, but the only thing is that for the text that's selected by VBA i would like to paste another value in it

thanks

Eric

**cytop** · Oct 6th 2016

Re: Help with if or loop

Quote

I have created a code that can realise this

Then post your code, I would like to see what you have done with the following lines:

Code

If Range("A3") = 2 Then 
    End If

Plus, it might help to explain what you are asking now...

If you post code then please use 'Code Tags'. These format and indent code making it easier to read. Your first post was edited by me but your turn now. To add code tags type '[noparse]

Code

[/noparse][/b]' before the code and '[b][noparse]

[/noparse][/b]' after.

test123ja · Oct 6th 2016

Re: Help with if or loop

Hi Cytop

I will explain hopefully well

In my Excelfile i have one cell. In this cell i can give values from 1 till 3. Every number is linked with a text in Word. What now happens is that, when i select number 2 in excel, al the three texts are shown in Word.

But what i want is that the text that is linked with number 2 and remove the other texts.

I hope i

The code is:

Code

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") = 1 Then
     With wDoc.Application.Selection
       .Find.Text = "<<OPTIONEEL1>>"
       If .Find.Execute Then
       .Text = Range("C4")
       End If
       .Find.Text = "<<A1>>"
       If .Find.Execute Then
       .Text = Range("D2")
    .EndOf wdWord, wdMove
      End If
      End With
      End If
'--------------------------------------------------------------------------------------------------------------------
      
      If Range("A3") = 2 Then
      With wDoc.Application.Selection
    .Find.Text = "<<OPTIONEEL2>>"
    If .Find.Execute Then
    .Text = Range("C5")
    End If
     .Find.Text = "<<A2>>"
       If .Find.Execute Then
       .Text = Range("D3")
       End If
       .EndOf wdWord, wdMove
       .Find.Text = "<<A3>>"
       If .Find.Execute Then
       .Text = Range("D4")
       End If
          .EndOf wdWord, wdMove
        
    End With
    End If
    
'----------------------------------------------------------------------------------------------------
    If Range("A3") = 3 Then
    With wDoc.Application.Selection
    .Find.Text = "<<OPTIONEEL3>>"
    If .Find.Execute Then
    .Text = Range("C6")
    .EndOf wdWord, wdMove
     
      
    End If
    End With
    End If 
end sub

Display More

**cytop** · Oct 6th 2016

Re: Help with if or loop

Quote

But what i want is that the text that is linked with number 2 and remove the other texts

I'm sorry - I really don't understand...

Can you explain what is to happen in words?

For example, if A3 = 2, the current code:

Searches for '<<OPTIONEEL2>>'. If found replaces with the text in C5
Searches for '<<A2>>'. If found replaces with the text in D3
Searches for '<<A3>>'. If found replaces with the text in D4

test123ja · Oct 6th 2016

Re: Help with if or loop

Hi Ctop

Thank you for the quick answer. I'm sorry for my bad explanation.

For example, if A3 = 2, the current code:
then

Searches for '<<OPTIONEEL2>> in a existing Microsoft Word document and replace it with data in Cell C4
In this existing Word document the only text in there are:

<<OPTIONEEL1>>
<<OPTIONEEL2>>
<<OPTIONEEL3>>

What is want is when in cell A3 is a 2 then show only the value from cell C5 the other two texts : <<OPTIONEEL1>> and <<OPTIONEEL3>> have to be deleted or hidden

**cytop** · Oct 6th 2016

Re: Help with if or loop

OK, so if you search for 1, you want to remove 2 & 3. If you search for 3, you want to remove 1 & 2.

I'm assuming these are place markers so that raises the question of what you want to do here. Just replace the text leaving the paragraph markers intact or remove the entire paragraph so extra (now) blank lines are removed?

If so, it seems simplest to just have 1 place marker and simply replace that with the contents of whatever cell is being used for the source text.

VBA - Help with If statement inside loop

Participate now!

Share