vba: geen waarde in kolom A / of om en om

[arjoderoon]

ik heb:

sheetnames = Array("uitdraai FD", "uitdraai asw", "uitdraai food")

For i = LBound(sheetnames) To UBound(sheetnames)
With Sheets(sheetnames(i))
    For Each cel In .Range(.[b2], .[b1000000].End(xlUp))
                If cel.Value(0, 0) > 0 Then
                cel.Offset(0, -1) = cel.Offset(0, 0) & cel.Offset(0, 1) & cel.Offset(0, 2)
                Else
               
                End If
    Next cel
End With
Next i

gebaseerd op een vergelijkbaar script dat werkt. Alleen bij bovenstaande krijg ik een error:

"het subscript valt buiten bereik" waarbij dit gearceerd wordt in de foutopsporing:
With Sheets(sheetnames(i))

ik snap alleen niet waarom...

edit: nu krijg ik deze error: fout 450: onjuist aantal argumenten of ongeldige eigenschappentoewijzing

en vervolgens wordt dit gearceerd:
If cel.Value(0, 0) > 0 Then

 

[arjoderoon]

ik blijf bezig. Nu ontdekte ik dat kolom A eerst leeg gemaakt moet worden voor de nieuw bijgewerkte waarde erin weg geschreven kan worden.
Dat dacht ik heel eenvoudige getackelt te hebben maar niet dus. kolom A blijft leeg als onderstaande is uitgevoerd:

Sub datakolomA()

Application.ScreenUpdating = True
Application.Calculation = xlCalculationManual

sheetnames = Array("uitdraai fd", "uitdraai asw", "uitdraai food")

For i = LBound(sheetnames) To UBound(sheetnames)
    With Sheets(sheetnames(i))
        .Columns(1).ClearContents
    End With
    With Sheets(sheetnames(i))
        For Each cel In .Range(.[b2], .[b1000000].End(xlUp))
                If cel(0, 0) <> "" Then
                cel.Offset(0, -1) = cel.Offset(0, 0) & cel.Offset(0, 1) & cel.Offset(0, 2)
                Else
               
                End If
        Next cel
    End With
Next i

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

End Sub

ik dacht al dat de code "clear contents in kolom A" misschien uitgevoerd blijft worden, maar ik kom er

nadat kolom A gewist is, moet er in kolom a een samenvoeging van de waardes uit kolom B,C en D komen.

De code loopt, ik krijg geen foutmelding, maar kolom A blijft leeg..

na nog een test: na de else heb ik ook een msgbox geprobeerd. Dan krijg ik ontelbaar veel msgboxen. (leuk om weg te klikken).
dus blijkbaar wordt de waarde in kolom B beschouwd als leeg?

vul ik vervolgens dit in als code (alleen het gewijzigde deel)

    With Sheets(sheetnames(i))
        .Columns(1).ClearContents
        For Each cel In .Range(.[b2], .[b1000000].End(xlUp))
                If cel(0, 0) <> "" Then
                
                Else
                cel.Offset(0, -1) = cel.Offset(0, 0) & cel.Offset(0, 1) & cel.Offset(0, 2)
                End If

dan komt er om en om in de regels wel een waarde terecht. waarom dit in de ene regel wel en in de andere regel niet is snap ik niet.

dus 2 dingen:
1. waarom beschouwd de macro de cellen in kolom B als leeg?
2. waarom komt er daarna om en om wel een waarde in kolom A terecht?

 

[snb]

Als bezoekers aan dit forum naar een bepaald onderwerp op zoek gaan lijkt het me onwaarschijnlijk dat ze als zoekterm 'wat doe ik verkeerd' (of een deelverzameling daaruit) zullen hanternen.

De kracht van fora zoals dit is, dat niet alleen vragen van vragenstellers worden beantwoord, maar ook vragen van zoekers in dit forum.
Het is daarom helemaal niet verkeerd een titel aan je vraag te geven die de lading dekt en anderen helpt soortgelijke vragen op te lossen door naar de antwoorden in jouw draad te kijken.

Dat lijkt me in ieder geval een eerste antwoord op de titel van deze draad.

 

[arjoderoon]

mijn excuses. bij deze is de titel gewijzigd

 

[MDN111]

Probeer eens door de 'For Each' - loop te wijzigen als volgt:

For Each cel In .Range([b2], [b1000000].End(xlUp)).Cells
                If cel <> "" Then
                cel.Offset(0, -1) = cel.Offset(0, 0) & cel.Offset(0, 1) & cel.Offset(0, 2)
                Else
               
                End If
        Next cel

Grtz,
MDN111.

 

[arjoderoon]

thanks. hij doet nu wel wat, alleen krijg ik direct een "fout 1004: door de toepassing of door object gedefinieerde fout", waarna bij de foutopsporing deze regel gemarkeerd wordt:
For Each cel In .Range([b2], [b1000000].End(xlUp)).Cells

voor de volledigheid, dit is de volledige code die uitgevoerd wordt:

Sub datakolomA()

Application.ScreenUpdating = True
Application.Calculation = xlCalculationManual

sheetnames = Array("uitdraai FD", "uitdraai asw", "uitdraai food")

For i = LBound(sheetnames) To UBound(sheetnames)
    With Sheets(sheetnames(i))
        .Columns(1).ClearContents
        For Each cel In .Range([b2], [b1000000].End(xlUp)).Cells
                If cel <> "" Then
                cel.Offset(0, -1) = cel.Offset(0, 0) & cel.Offset(0, 1) & cel.Offset(0, 2)
                Else
               
                End If
        
        Next cel
    End With
Next i

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

End Sub

 

[arjoderoon]

solved!

de oplossing zat in het .range stuk.

voor de [b2] en voor de [b1000000] moest nog een punt... daarna doet hij het goed.

 

[snb]

waarom zet je in kolom A niet de formule

=B2&C2&D2
en dan dubbelklikken op het doortrekken kruisje ?

of

Sub M_snb()
  for each sh in sheets(Array("uitdraai FD", "uitdraai asw", "uitdraai food"))
    sn=sh.cells(1).currentregion.offset(1).columns(1)
    sp=sh.cells(1).currentregion.offset(1)

    for j=1 to ubound(sn)
      sn(j,1)=sp(j,2)&sp(j,3)&sp(j,4)
    next

    sh.cells(1).currentregion.offset(1).columns(1)=sn
  next
End Sub

 

[arjoderoon]

bedankt voor de suggestie. ik had in die kolom dat staan, alleen omdat de template gebruikt werd voor meerdere data queries, en het aantal regels nogal verschilt per query, liep ik het risico regels te missen als het bestand ververst wordt (en er daardoor regels wijzigen / bijkomen).

Daarvoor dient de macro: om ervoor te zorgen dat er steeds in kolom a de samenvoeging komt, maar dat wel alle gevulde regels meegenomen worden.

Plus dat als de queries ververst worden, de samenvoeging in kolom A ook ververst wordt. Dat was niet het geval, en daarom heb ik de columns(1).clearcontents toegevoegd. > zodat kolom A eerst helemaal gewist wordt en daarna de samenvoeging opnieuw bepaald.

ik begrijp dat de geadviseerde code sneller/efficiënter is dan wat ik nu heb?

 

[snb]

Vele malen.

 

[arjoderoon]

bedankt. op basis van bovenstaande gok ik zo dat al mijn bestanden nog een heel stuk beter kunnen.

 

[arjoderoon]

ik probeer nog wat toe te voegen voor de samenvoeging die in de kolommen B en C moet komen.
In kolom B moet een samenvoeging komen van de inhoud in kolom 4,5 en 6.
in kolom C moet een samenvoeging komen van de inhoud in kolom 4,5 en de inhoud uit een kolom op basis van een als formule:
als(h2=0;als(g2=0;F2;G2);H2).

(waarbij er nu nog gekeken wordt naar als = 0, maar dat moet worden als = "")

Ik probeerde de macro al aan te passen dat deze voor meerdere kolommen het doet. alleen lukte dat niet. En op basis van dat de samenstelling steeds anders is, gok ik dat ik daarvoor 3 verschillende loops nodig heb.

voor Kolom A loopt de code gewoon. Daar komt de gewenste samenvoeging (welke een waarde, geen verwijzing..).
Maar als dan de loopt voor kolom B uitgevoerd moet worden krijg ik een fout:


het subscript valt buiten bereik

waarna deze regel gemarkeerd wordt:
sn(j, 2) = sp(j, 4) & sp(j, 5) & sp(j, 6)

de code zoals ik deze nu aangepast heb:

For Each sh In Sheets(Array("blad3"))
    sn = sh.Cells(1).CurrentRegion.Offset(1).Columns(1)
    sp = sh.Cells(1).CurrentRegion.Offset(1)


    For j = 1 To UBound(sn)
      sn(j, 1) = sp(j, 4) & sp(j, 5)
    Next

    sh.Cells(1).CurrentRegion.Offset(1).Columns(1) = sn
  Next


For Each sh In Sheets(Array("blad3"))
    sn = sh.Cells(1).CurrentRegion.Offset(1).Columns(2)
    sp = sh.Cells(1).CurrentRegion.Offset(1)


    For j = 1 To UBound(sn)
      sn(j, 2) = sp(j, 4) & sp(j, 5) & sp(j, 6)
    Next

    sh.Cells(1).CurrentRegion.Offset(1).Columns(2) = sn
  Next

mijn vragen:
-hoe moet ik de code aanpassen voor de 2e kolom?
-hoe voeg ik voor de derde kolom de voorwaardelijke bepaling toe aan de code?

[edit] sorry, ik snapte current region niet helemaal. Maar om dit voor meerdere kolommen te doen werkt currentregion niet zeker? Aangezien het volledige aangrenzende gebied geselecteerd wordt.