trefwoord toevoegen aan geselecteerde cellen in range

[littlepete]

hallo

in mijn adressenlijst heb ik een kolom AZ die trefwoorden bevat om op te filteren.
ik probeer nu een macro te construeren die via een inputbox een nieuw trefwoord toevoegt
nadat een selectie is toegevoegd...

Dim dezerng As Range
dezerng = Range("rngmededelingen").SpecialCells(xlCellTypeVisible)
...
With dezerng
.Value = .Value & "gezocht"
End With

ik ken (nog) niet genoeg van vba om de fout te zien...
al bedankt voor jullie reactie !

peter, belgie
_________
in redelijkheid bereik je meer !
pjmb@telenet.be

 

[Jerry Sullivan]

hallo Peter,

 Dim dezerng As Range
 
 Set dezerng = Range("rngmededelingen").SpecialCells(xlCellTypeVisible)

 If dezerng Is Nothing Then
   MsgBox "Geen cellen gevonden"
 Else
   With dezerng
    .Value = Evaluate(.Address(0, 0) & " & ""gezocht""")
   End With
 End If

 

[littlepete]

dit is een verbeterde versie, die helaas ook niet werkt ... :
ik denk dat het rode gedeelte ergens een fout bevat ...

bedoeling is dat inputbox een trefwoord vraagt dat dan wordt TOEGEVOEGD aan elke cel in de op dat moment gefilterde range ...
die filtering gebeurt tevoren, niet met deze macro ...

bedankt voor jullie hulp !!!


Sub trefwoorden()
Dim gezocht As String
Dim cl As Range
Dim rng As Range
Set rng = Range("rngmededelingen")
title = "TOEVOEGEN TREFWOORD"
prompt = "TREFWOORD TOEVOEGEN:" & Chr(10) & "__________" & Chr(10) & "[0] menu verlaten."
caption = ""
gezocht = InputBox(prompt, "TREFWOORD TOEVOEGEN", caption, 7000, 6000)

For Each cl In rng
With ActiveSheet.Range("rngmededelingen").SpecialCells(xlCellTypeVisible)
.Value = .Value & " " & gezocht
End With
Next cl

End Sub

 

[Jerry Sullivan]

Mijn fout. Probeer ...

Sub trefwoorden()
 Dim gezocht As String, prompt As String
 Dim Caption As String, Title As String
 
 Dim cl As Range
 Dim rng As Range
 
 Set rng = Range("rngmededelingen").SpecialCells(xlCellTypeVisible)
 If rng Is Nothing Then
   MsgBox "Geen cellen gevonden"
   Exit Sub
 End If
 
 Title = "TOEVOEGEN TREFWOORD"
 prompt = "TREFWOORD TOEVOEGEN:" & Chr(10) _
    & "__________"  & Chr(10) & "[0] menu verlaten."
 Caption = ""
 
 gezocht = InputBox(prompt, "TREFWOORD TOEVOEGEN", Caption, 7000, 6000)
 
 For Each cl In rng
      cl.Value = cl.Value & " " & gezocht
 Next cl

End Sub

 

[littlepete]

hello


i plan to rebuild the content of my column with words and put them in a certain order ...

in stead of putting the words manually in, i can filter and then use the macro to add that word each time

and ...

the macro works perfectly well now !!! i do struggle with that with rng... end with formula ...

but that's now one problem less !!!

thanks for the help !!!

feel free to read my other messages and enjoy the coming weekend !!!

pete,
belgium

 

[littlepete]

PROBLEM SOLVED

to append a word, that you fill in with an inputbox, to a list of words in column AZ, after (or not) setting a filter :
om een woord aan een trefwoordenlijst toe te voegen, na invullen van inputbox, in kolom AZ, met of zonder gebruik van een filter :

Sub trefwoorden()
Dim gezocht As String
Dim rng As Range
title = "TOEVOEGEN TREFWOORD"
prompt = "TREFWOORD TOEVOEGEN:" & Chr(10) & "__________" & Chr(10) & "[0] menu verlaten."
caption = ""
gezocht = InputBox(prompt, "TREFWOORD TOEVOEGEN", caption, 7000, 6000)
Set rng = Range("$az5", Range("$Az375").End(xlUp)).SpecialCells(xlCellTypeVisible)
With rng.SpecialCells(xlCellTypeVisible)
.Value = .Value & " " & gezocht
End With
End Sub


hope you can use it !

pete,
Belgium

 

[littlepete]

hello


sometimes i really have a feeling that someone hacks my computer and messes up my excel !!!


in this list of threads you can see i fount the solution to add a word to my column of words (to use for filtering).

today i see it doesnt work

why and HOWWWW can this be wrong :

.value = .value & gezocht
(dim gezocht as string at start of the macro

it worked perfectly well...
THE ONLY THING that changed is the column, it was AZ before, it is BF now ... both have string capacities and all other stuff is the same ...

this is the whole macro:

Sub trefwoorden()
'
' cel verwijzingen aanpassen na invoegen / verwijderen van kolommen !
'
Dim gezocht
Dim dezerng As Range
Title = "TOEVOEGEN TREFWOORD"
prompt = "TREFWOORD TOEVOEGEN:" & Chr(10) & "__________" & Chr(10) & "[0] menu verlaten."
Caption = ""

gezocht = InputBox(prompt, "TREFWOORD TOEVOEGEN", Caption, 7000, 6000)

Set dezerng = Range("$bf5", Range("$bf375").End(xlUp)).SpecialCells(xlCellTypeVisible)
With dezerng.SpecialCells(xlCellTypeVisible)
.Value = .Value & " " & gezocht
End With
End Sub

thanks for helping (again) ...
pete

 

[Jerry Sullivan]

hallo Pete,

The problem isn't the change to column BF. This code and the previous one you posted will error if the Range of Visible Cells is not continguous. (Google Translate says this is "aangrenzend" in Dutch).

So when the code worked for you, there may not have been any rows hidden within that range.

You'll need to step through each cell like I did in Post #4; or
if you have a lot of cells, you can step through each Area which is more efficient....

 Dim i As Long
 
 Set dezerng = Range("bf5", _
   Cells(Rows.Count, "bf").End(xlUp)).SpecialCells(xlCellTypeVisible)
 
 With dezerng
   For i = 1 To .Areas.Count
      With .Areas(i)
         .Value = Evaluate(.Address(0, 0) & " & """ & gezocht & """")
      End With
    Next i
 End With

 

[littlepete]

hello jerry

it doesnt work...

when i do a find and replace in my sheet for the new word, it doesnt find it anywhere...
i have no idea how to read, understand or change that evaluate function...

thanks and good luck !

pete,
belgium

 

[Jerry Sullivan]

Pete,

Is there any reason you're using find-replace on the sheet to test if the code worked? It's more reliable to just look at the visible cells in Col BF after the code is run to see if they had the string added to the existing values. Using find-replace has the possibility of mismatch with "Match Case" or "Match entire contents"

Whether that worked or not, you'll probably be better off with code that you understand better than the evaluate function.
The slight difference in speed won't matter unless you are changing thousands of cells at a time.

Try...

 Set dezerng = Range("bf5", _
   Cells(Rows.Count, "bf").End(xlUp)).SpecialCells(xlCellTypeVisible)
 
 If dezerng Is Nothing Then
   MsgBox "Geen cellen gevonden"
 Else
   For Each cl In dezerng
      cl.Value = cl.Value & " " & gezocht
   Next cl
 End If