cel referentie in vba plaatsen

[Ropw]

Hoop dat iemand de oplossing heeft.

Na filtering gegevens (SHIFT-ALS-L) en verplaatsing van de cursor kom ik bij de eerste cel in kolom A. In onderstaand VBA kom ik dan uit op A118.
Maar door bv weggooien van regels verandert die A118 in A115. Hoe krijg ik die waarde dan in de geselecteerde range? (dus overal waar 118 staat moet dan 115 komen)

Sheets("containers").Select
Range("A118:L118").Select
Range("L118").Activate
Range(Selection, Selection.End(xlDown)).Select
Range("L118:AU10000").Select
Application.CutCopyMode = False
Selection.Copy

 

[Joske2000]

i = Range("A" & Rows.Count).End(xlUp).Row
Sheets("containers").Select
Range("A" & i & ":L" & i).Select
Range("L" & i).Activate
Range(Selection, Selection.End(xlDown)).Select
Range("L" & i & ":AU10000").Select
Application.CutCopyMode = False
Selection.Copy

Vermijd het gebruik van select en activate in je code.
Dit werkt vertragend.

Gr,

Joske

 

[Haije]

Ropw,

welkom op Helpmij.nl!

Het is een goede gewoonte om een vraag te illustreeren aan de hand van een (voorbeeld)excelbestand waarin het "probleem" zich voordoet.

Want ik denk dat de oplossing van Joske niet is wat je zoekt.....

 

[Ropw]

i = Range("A" & Rows.Count).End(xlUp).Row
Sheets("containers").Select
Range("A" & i & ":L" & i).Select
Range("L" & i).Activate
Range(Selection, Selection.End(xlDown)).Select
Range("L" & i & ":AU10000").Select
Application.CutCopyMode = False
Selection.Copy

Vermijd het gebruik van select en activate in je code.
Dit werkt vertragend.

Gr,

Joske

dank je Joske, duur even ook om het forum te leren kennen.
jouw oplossing komt aardig in de richting. Alleen na controle mis ik een aantal regels als ik regels heb weggegooid. dit klinkt verwarrend. Ik probeer het uit te leggen.
ik ben nog een beginner en werk bijna alleen met de recorder.


met filtering op kleur (opvulling met celeigenschappen) . stel dat 16 t/m 19 groen zijn en de overige rood. dan maak ik 2 selecties die ik weer in een ander sheet onderbreng.
eerste selectie (op de rodekleur) geeft dan de regels 1 t/m 15 en 20 t/m 23. tweede selectie geeft dan 16 tm 19
dat gaat allemaal goed in jouw vba. maar dan gooi ik regel 2 weg en dan begint de 2e selectie opeens (na plaatsing in een nieuw sheet) met 21. Dus 20 wordt niet meer in overzicht opgenomen.
Hoe nu verder?

TITELNAAM 2017

NR NAAM

1 testnaam 1 J J
exit testnaam 2 J J
2 testnaam 3 N N
13 testnaam 4 J J
14 testnaam 5 J J
15 testnaam 6 J J
16 testnaam 7 N N
17 testnaam 8 N N
18 testnaam 9 J N
19 testnaam 10 N N
20 testnaam 11 J J
21 testnaam 12 N N
22 testnaam 13 N N
23 testnaam 14 N N

 

[Ropw]

Ropw,

welkom op Helpmij.nl!

Het is een goede gewoonte om een vraag te illustreeren aan de hand van een (voorbeeld)excelbestand waarin het "probleem" zich voordoet.

Want ik denk dat de oplossing van Joske niet is wat je zoekt.....

dag Haije,

bedankt voor je reactie
paar vraagjes:
die pop-up advertenties zijn die blijvend uit te schakelen?

wil graag illustreren, maar zie geen mogelijkheid om plaatjes te plakken. Zit dat ergens verstopt of hoe doe ik dat ?
mvrgr rop

 

[Haije]

rop,

het illustreren moet je niet te letterlijk nemen.

helpers hebben niet zo veel met plaatjes, maar wel met excelbestanden.

Bijlagen kun je toevoegen in het Advanced scherm, via de paperclip

 

[Ropw]

plaatje voorbeeld

 

[VenA]

Uit het plaatje blijkt nagenoeg niets dat met de vraag te maken heeft. Plaats een representatief voorbeeldbestand.

 

[Ropw]

plaatje 2

 

[VenA]

Gaat een lang verhaal worden zo.

 

[Haije]

rop, plaats svp je excelbestand....

 

[Ropw]

Gaat een lang verhaal worden zo.

een goed verstaander heeft maar een half woord nodig. Mogelijk ben jij niet diegene die me gaat helpen.

 

[VenA]

In VBA zal het zo ongeveer uitzien.

Sub VenA()
With Sheets("Containers")
  .Unprotect
  For Each sh In Sheets(Array("FRANSPIET", "VONK"))
    sh.Unprotect
    sh.UsedRange.Clear
    With .Cells(5, 1).CurrentRegion
      .AutoFilter 1, IIf(sh.Name = "FRANSPIET", RGB(226, 239, 218), RGB(242, 242, 242)), xlFilterCellColor
      .Offset(1).Resize(, 4).Copy sh.[A6]
      .AutoFilter
    End With
    sh.Protect
  Next sh
  .Protect
End With
End Sub

Edit bestandje nog niet bekeken.

 

[Ropw]

Bekijk bijlage helpmijexcelapplsperen.xlsm

 

[Ropw]

In VBA zal het zo ongeveer uitzien.

Sub VenA()
With Sheets("Containers")
  .Unprotect
  For Each sh In Sheets(Array("FRANSPIET", "VONK"))
    sh.Unprotect
    sh.UsedRange.Clear
    With .Cells(5, 1).CurrentRegion
      .AutoFilter 1, IIf(sh.Name = "FRANSPIET", RGB(226, 239, 218), RGB(242, 242, 242)), xlFilterCellColor
      .Offset(1).Resize(, 4).Copy sh.[A6]
      .AutoFilter
    End With
    sh.Protect
  Next sh
  .Protect
End With
End Sub

Edit bestandje nog niet bekeken.

Fijn bedankt VenA werkt bijna correct. Alleen de bovenste 5 regels verdwijnen totaal. mijn titel blokkering staat in D6

 

[VenA]

.UsedRange.Offset(5).Clear

 

[Ropw]

respect voor je en bijzonder slim, maar het is me nog niet duidelijk waar ik deze code moet plaatsen of moet veranderen.
ik heb het nu in de 5e regel aangepast. is dat juist? macro loopt dan vast
Verder neemt hij de gegevens niet mee van L6 t/m aw10000

With Sheets("Containers")
.Unprotect
For Each sh In Sheets(Array("FRANSPIET", "VONK"))
sh.Unprotect
sh.UsedRange(5).Clear
With .Cells(5, 1).CurrentRegion
.AutoFilter 1, IIf(sh.Name = "FRANSPIET", RGB(226, 239, 218), RGB(242, 242, 242)), xlFilterCellColor
.Offset(1).Resize(, 4).Copy sh.[A6]
.AutoFilter
End With
sh.Protect
Next sh
.Protect
End With

 

[VenA]

sh.UsedRange(5).Clear

lijkt niet op wat er in #16 staat. Dus iets over een goed verstaander en halve woorden slaat op? Wat moet er met L6 t/m aw10000 gebeuren? Ook een kopieeractie naar het betreffende blad?
Dan zal je een extra regel moeten toevoegen

.Offset(1,11).Resize(, 38).Copy sh.[L6]

Even twee Exceltips
- Gebruik bij voorkeur geen samengevoegde cellen; (gaat vaak niet goed in combinatie met VBA en formules)
- Zet de kolomkoppen direct boven de gegevens. (maakt het bepalen van o.a de currentregion eenvoudiger)

Even twee forumtips:
- Niet onnodig quoten; (lijkt mij voor zich spreken)
- Code hoor je tussen codetags plaatsen. (maakt de code leesbaar en wordt binnen een eigen kader geplaatst.)

 

[Ropw]

dank weer ook voor de tips. het is wat lastiger dan samengevoegde cellen. tussen D en L zitten nog wat kolommen zie zichtbaar worden in sheet sheet container met ctrl-shift D.
dat maakt het er niet eenvoudiger op.

De cellen L6 tm AU10000 moeten naar de corresponderende cellen van VONK

ik ben een leek dus zeker een slecht verstaander
een gek kan meer vragen dan een wijs man kan antwoorden, dus laat je door mij niet gek maken

de regel sh.UsedRange.Clear welke range is dat dan en Offset heb ik ook weinig kaas van gegeten. wat wordt er resized?
ik heb de regels er nu zo goed tussengezet !?
With Sheets("Containers")
.Unprotect
For Each sh In Sheets(Array("FRANSPIET", "VONK"))
sh.Unprotect
sh.UsedRange.Clear
.Offset(1, 11).Resize(, 38).Copy sh.[L6]
sh.UsedRange(5).Clear
With .Cells(5, 1).CurrentRegion
.AutoFilter 1, IIf(sh.Name = "FRANSPIET", RGB(226, 239, 218), RGB(242, 242, 242)), xlFilterCellColor
.Offset(1).Resize(, 4).Copy sh.[A6]
.AutoFilter
End With
sh.Protect
Next sh
.Protect
End With

 

[snb]

zet je code tussen codde tags, zo:

With Sheets("Containers")
  .Unprotect
  For Each sh In Sheets(Array("FRANSPIET", "VONK"))
   sh.Unprotect
   sh.UsedRange.Clear
   .Offset(1, 11).Resize(, 38).Copy sh.[L6]
   sh.UsedRange(5).Clear
   With .Cells(5, 1).CurrentRegion
      .AutoFilter 1, IIf(sh.Name = "FRANSPIET", RGB(226, 239, 218), RGB(242, 242, 242)), xlFilterCellColor
      .Offset(1).Resize(, 4).Copy sh.[A6]
      .AutoFilter
   End With
   sh.Protect
  Next sh
  .Protect
End With