excel vba range controleren op inhoud met if then

[Patje76!]

Goedemorgen,

Met VBA wil ik controleren of een cel in een bereik is gevuld met tekst of een getal, als dit zo is wil ik een vaste waarde schrijven in een andere cel. (vb. ik wil controleren of er een bestelnummer aanwezig is zo ja dan moet er in een andere cel NL geplaatst worden)

Vanuit een CCV winkel krijg ik een export (excell) van de bestellingen met afleveradressen.
Deze moet omgezet worden in en andere indeling en vervolgens als CSV opgeslagen worden om te kunnen uploaden naar de transporteur.

Het omzetten naar een andere indeling doe ik als volgt:


Dit werkt.

Nu wil ik een cel in een range controleren en vervolgens NL in een andere range schrijven.

Of met:


Beide werken niet.
Het lijkt erop dat ik in de range niet kan zoeken naar =waar of =>0
Moet ik dit op een andere manier doen?

[breew]

wil je weten of er iets in de cel staat?
dan kun je cheken op

cel.value <>""

of

IsEmpty( cel.value )

Dit geldt voor één cel. Wil je een range checken, dan moet je (denk ik) een foreach loop gebruiken.

Ik zou eerst de source-data 1-op-1 kopiëren naar je target, en dan in de target-ranges zoeken naar lege cellen.

[ Voor 64% gewijzigd door breew op 03-07-2018 11:45 ]

[Patje76!]

Ik heb een test gedaan met een messagebox om te controleren of de range wordt gecontroleerd.
Dit gebeurt en geeft in een messagebox een melding.


Nu is het de bedoeling als er iets in de P range staat, dan moet in de range H "NL"geschreven worden.

Bv P2 en P3 en P4 zijn gevuld dam moet er in H2 - H3 en H4 "NL"geschreven worden.
Als er in P 10 rijen zijn gevuld moet er ook in H 10 rijen gevuld worden met "NL"

[breew]

eenvoudige check is om de range P naar een array te schrijven

Visual Basic:

Dim varArray As Variant
varArray = Range("A1:A5").Value

en dan met een loop-je door de array gaan en de inhoud wijzigen als er een waarde is

Visual Basic:

Dim i As Long
For i = LBound(varArray) To UBound(varArray)
  If varArray(i) <> "" Then varArray(i) = "NL"
Text i

en dan uiteindelijk de varArray weer plakken op je doel-range (wel eerst transponeren)

Visual Basic:

DoelSheet.Range("B1:B5") = WorksheetFunction.Transpose(varArray)

Als je bovenstaande voor een (groot) aantal ranges moet doen, maar er dan een private Sub van met twee argumenten: rngInput en rngOutput
Zodat je alleen bron en doel op hoeft te geven, en de sub doet de rest (array aanmaken, checken, aanpassen, transponeren en wegschrijven)

[ Voor 22% gewijzigd door breew op 03-07-2018 13:23 ]

[Coffeemonster]

Je kunt ook direct door de range lopen met een For Each-loop:

Visual Basic:

For Each c in Range("A4:A30") 
 If c.Value <>"" Then 
 <DoSomething>
 End If 
Next c

Je kunt lege cellen ook met de functie ISBLANK identificeren.

[ Voor 16% gewijzigd door Coffeemonster op 03-07-2018 23:54 ]

[F_J_K]

offtopic:
welkom! Geef code ajb NIET als plaatje maar als tekst. Mooiste is dan tussen [ code = vb ] ...[ / code ] blok zonder de spaties. Dat is veel beter leesbaar, is copypastebaar, en bestaat ook over een jaar nog als het plaatje dood is. Zie ook hoe @breew en ook @Coffeemonster dat doet.

[Unusable]

Hoe ik dit lees is dat je twee cellen wilt controleren namelijk cel A4 en A30.
Je wilt deze beide bekijken of deze groter zijn dan een bepaalde waarde of true in 1 statement.

Misschien kan je het beter uit elkaar trekken en de waardes apart bekijken en dan de 2 waardes vullen zoals:

Visual Basic:

Sub Macro1()
If (sourceSheet.Range("A4").Value > 0 And sourceSheet.Range("A30").Value > 0) Then
sourceSheet.Range("H2") = "NL"
sourceSheet.Range("H27") = "NL"
End If
End Sub

Succes

Aanpassing nodig, sourceSheet.Range("H2","H27") = "NL" vulde alle waardes van H2 t/m H27

[ Voor 27% gewijzigd door Unusable op 04-07-2018 10:25 ]

[Patje76!]

Als ik de Array van @breew gebruik dan krijg ik de melding: Subscript valt buiten het bereik????
[code = VB]
Dim varArray As Variant
varArray = Range("P2:P27").Value

Dim i As Long
For i = LBound(varArray) To UBound(varArray)
If varArray(i) <> "" Then varArray(i) = "NL"
Next i

targetSheet.Range("H2:H27") = sourceSheetFunction.Transpose(varArray)
[/ code]

Met de For Each-loop van @Coffeemonster kom ik een stuk verder, maar dan wordt de gehele range H2-H27 volgeschreven terwijl er in P2-P27 bv alleen de eerst 2 zijn gevuld.
[code = VB]
For Each c In Range("P2:P27")
If c.Value <> "" Then
targetSheet.Range("H2:H27").Value = "NL"
End If
Next c
[/ code]

Als ik onderstaande regel toevoeg werkt het wel, maar dat zou betekenen dat ik 26 losse regels toe moet voegen, moet toch op een andere manier kunnen lijkt mij?
[code = VB]
If targetSheet.Range("P2").Value > 0 Then targetSheet.Range("H2").Value = "NL"
[/ code]

[Coffeemonster]

Patje76! schreef op woensdag 4 juli 2018 @ 23:54:
Als ik de Array van @breew gebruik dan krijg ik de melding: Subscript valt buiten het bereik????
[code = VB]
Dim varArray As Variant
varArray = Range("P2:P27").Value

Dim i As Long
For i = LBound(varArray) To UBound(varArray)
If varArray(i) <> "" Then varArray(i) = "NL"
Next i

targetSheet.Range("H2:H27") = sourceSheetFunction.Transpose(varArray)
[/ code]

Met de For Each-loop van @Coffeemonster kom ik een stuk verder, maar dan wordt de gehele range H2-H27 volgeschreven terwijl er in P2-P27 bv alleen de eerst 2 zijn gevuld.
[code = VB]
For Each c In Range("P2:P27")
If c.Value <> "" Then
targetSheet.Range("H2:H27").Value = "NL"
End If
Next c
[/ code]

Als ik onderstaande regel toevoeg werkt het wel, maar dat zou betekenen dat ik 26 losse regels toe moet voegen, moet toch op een andere manier kunnen lijkt mij?
[code = VB]
If targetSheet.Range("P2").Value > 0 Then targetSheet.Range("H2").Value = "NL"
[/ code]

Die c in de if-thenregel verwijst in dit geval steeds naar de volgende cel in die range. Dat betekent dat je met c.Value die specifieke waarde terugkrijgt, en met c.offset kun je dan een cel verderop in de rij een andere waarde geven. Je moet dus niet de hele range H2:H27 een andere waarde geven, maar alleen de cel waarnaar je met c.offset naar wijst.

[Patje76!]

@Coffeemonster TOP! Nu werkt het wel!
[code = vb]
For Each c In Range("P2:P27")
If c.Value <> "" Then
c.Offset(0, -8).Value = "NL"
End If
Next c
[/ code]

[Reptile209]

Patje76! schreef op donderdag 5 juli 2018 @ 09:57:
@Coffeemonster TOP! Nu werkt het wel!
[code = vb]
For Each c In Range("P2:P27")
If c.Value <> "" Then
c.Offset(0, -8).Value = "NL"
End If
Next c
[/ code]

Let wel op dat je nu een 'magic number' in je code introduceert: -8. En trouwens ook je range P2:P27. Als over een jaar iemand een kolom of rij invoegt of verwijdert in de sheet, dan is je macro stuk (of liever gezegd: dan schrijft hij naar de verkeerde kolom). Dat hoeft geen ramp te zijn, maar wees je er wel van bewust! Een betere aanpak is om één variabele (constante) bovenin je macro te zetten en die steeds in je code te gebruiken. Dan hoef je je magic number maar op één plaats aan te passen.
Nog beter is om de code zelf uit te laten vogelen welke kolom het moet zijn, maar da's een hoop meer werk.

Edit: hetzelfde geldt overigens voor de tekst "NL", misschien dat je die ook ooit nog eens moet veranderen. Zou je ook een constante van kunnen maken, maar bij strings is het meer geaccepteerd om ze zo in je code te houden. Of maak het meteen generieker met een lookup-tabelletje waarin ook DE, FR, UK, BE, etc. staan .

[ Voor 16% gewijzigd door Reptile209 op 05-07-2018 10:08 ]

[Patje76!]

Bedankt voor de tip! ik zal er rekening mee houden, maar ik verwacht niet dat er veel in de sheet gaat veranderen.