VBA vraagje (columns, rows, cells)

[Mswinkels]

Beste Forum lezers,

In mijn workbook probeer ik binnen een range die altijd start vanaf cell B9 tot en met lastRow en lastCol de inhoud van de cellen te controleren op een aantal voorwaarden die per kolom anders zijn.
De voorwaarde kunnen zijn "Upper Case", "Mixed Case", "Number","Date", Of een aantal vaste waardes.
Per kolom staan deze voorwaarde altijd beschreven in row 6.
Er zijn ongeveer 60 Sheets waar de kolommen en de voorwaarde variabel zijn. En de functie moet enkel werken voor de active sheet.

Met onderstaande code ben ik al enige tijd bezig geweest en heb van alles geprobeerd zonder succes.


De bedoeling is dus dat een kolom moet voldoen aan de voorwaarde die vast Staat in row 6 van die kolom. En zo elke kolom tot en met de laatste controleren.

Hopelijk hebben jullie de oplossing .

Sub tst()
Dim LastRow As Long 
Dim LastCol As Long 
Dim MyRange As Range

LastCol = ActiveSheet.Range("XFD8").End(xlToLeft).Column
LastRow = ActiveSheet.Cells(Rows.Count, "B").End(xlUp).Row
Set MyRange = ActiveSheet.Range("B9", Cells(LastRow, LastCol))

For Each Cell In MyRange
??????????????
If cell.column

    Else
    End If
Next

End Sub

 

[Cobbe]

Dit zal ook wel voldoen zeker:

Sub tst()
Dim cl As Range
For Each cl In ActiveSheet.UsedRange
cl.Select
  If cl = UCase(cl) Then MsgBox ("Hoofdletters")
  If cl = LCase(cl) Then MsgBox ("Kleine Letters")
Next
End Sub

 

[Mswinkels]

Dit zal ook wel voldoen zeker:

Sub tst()
Dim cl As Range
For Each cl In ActiveSheet.UsedRange
cl.Select
  If cl = UCase(cl) Then MsgBox ("Hoofdletters")
  If cl = LCase(cl) Then MsgBox ("Kleine Letters")
Next
End Sub

Hallo Cobbe,

Dank voor je snelle reactie. Maar dit is niet wat ik bedoel.
Ik heb als voorbeeld een print Screen toegevoegd.




Het is dus de bedoeling dat alle cellen vanaf regel 9 voldoen aan de voorwaarde die in regel 6 staan.
Rows 1 t/m 8 moeten niet aangepast worden. ( Dit zijn de headers van de sheet waarin Staat waaraan de gegevens in die kolom aan moeten voldoen.)
Column A mag ook niet aangepast worden.
Om het extra lastig te maken verschillende de voorwaarde per sheet. Bijvoorbeeld in sheet 1 moet kolom H Upper case zijn. Maar in sheet 2 moet kolom H een Datum zijn.
Daarnaast bedoel ik met kolom regel 9 en alles daar onder.

Hopelijk heb ik het duidelijk omschreven .

 

[Mswinkels]

 

[Cobbe]

Kan je niet een uitgekleed voorbeeldbestand posten?

 

[Mswinkels]

Bekijk bijlage Voorbeeld.xlsm

Wist niet hoe.
Kijken of het nu wel is gelukt.

 

[snb]

@Cobbe

Ik denk dat het ook wel zonder 'cl.Select' kan.

 

[snb]

Gebruik deze struktuur:

Sub M_snb()
   sn = Cells(1).CurrentRegion
   
   For j = 9 To UBound(sn)
      For jj = 2 To UBound(sn, 2)
         if sn(j,jj)= sn(6,jj) then .....
      Next
   Next
End Sub

NB. svp niet citeren/quoten in je reaktie.
Gezien je eigen code denk ik dat je dit beter kunt uitbesteden.

 

[Cobbe]

@snb

@Cobbe

Ik denk dat het ook wel zonder 'cl.Select' kan.

Natuurlijk lukt dat zonder select, maar had die er tussen gezet om te tonen over welke cel de boodschap liep.

 

[Mswinkels]

Hallo SNB.

Zou je mischien kunnen beschrijven wat de code doet zodat ik begrijp wat er Staat?


Stel in de cell D6 Staat de text "Upper Case".
Hoe maak ik de cellen D9 t/m D150 dan Ucase middels jou code?
Ik begrijp dat ik na de then moet aangeven wat er moet gebeuren. Ik begrijp alleen niet hoe de vergelijking wordt gemaakt met de tekst in regel 6?



Alvast bedankt.

 

[edmoor]

Ik heb een voorbeeldje voor je gemaakt voor Sheet1, kolommen D en F. Wanneer er in die kolommen iets wordt ingevuld gaat hij kijken in D6 of F6 wat er moet gebeuren en handel ernaar. Zoiets kan je voor iedere kolom doen. Is dat wat je bedoeld?

Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False
    Select Case Target.Column
        Case 4
            Call CellCasing(Target.Address, "D6")
        Case 6
            Call CellCasing(Target.Address, "F6")
        End Select
    Application.EnableEvents = True
End Sub

Sub CellCasing(Cel As String, luCasing As String)
    Select Case LCase(Range(luCasing).Value)
        Case "upper case"
            Range(Cel) = UCase(Range(Cel))
        Case "lower case"
            Range(Cel) = LCase(Range(Cel))
        Case "mixed case"
            'Bij Mixed Case hoeft er uiteraard niets te gebeuren
    End Select
End Sub

 

[Mswinkels]

Ik heb een voorbeeldje voor je gemaakt voor Sheet1, kolommen D en F. Wanneer er in die kolommen iets wordt ingevuld gaat hij kijken in D6 of F6 wat er moet gebeuren en handel ernaar. Zoiets kan je voor iedere kolom doen. Is dat wat je bedoeld?

Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False
    Select Case Target.Column
        Case 4
            Call CellCasing(Target.Address, "D6")
        Case 6
            Call CellCasing(Target.Address, "F6")
        End Select
    Application.EnableEvents = True
End Sub

Sub CellCasing(Cel As String, luCasing As String)
    Select Case LCase(Range(luCasing).Value)
        Case "upper case"
            Range(Cel) = UCase(Range(Cel))
        Case "lower case"
            Range(Cel) = LCase(Range(Cel))
        Case "mixed case"
            'Bij Mixed Case hoeft er uiteraard niets te gebeuren
    End Select
End Sub

Hallo Edmoore.

Dit werkt inderdaad goed.
Maar nog niet helemaal wat ik bedoelde.

De code moet zelf de target.column zoeken. En daar dus zelf het target.address mee bepalen.
En als het mogelijk is de private Sub zo maken dat hij niet in elke sheet hoeft te staan. Het gaat altijd alleen om de activesheet.
Op elke sheet zit een knop om de procedure af te trappen.

Je hebt me al wel een eind op weg geholpen.

 

[edmoor]

Het is nu een event routine en dat kan uiteraard ook anders als je het met een knop wilt starten. Maar hoe moet het programma weten dat, wanneer de activecell een cell in Column 4 is dat hij dan naar de inhoud van D6 moet kijken? Voor het regelen van de upper- en lowercase ligt dat alleen daar vast dus dat mag vast geprogrammeerd worden.

Wat je denk ik bedoeld is dat wanneer de activecell een cell in Column 4 is hij dan de condities 1 t/m 8 allemaal moet uitvoeren. En hoe je dat kan doen zit dus in mijn voorbeeld. Of het een event routine is of achter een knop zit maakt daarbij niet uit. In plaats van te kijken naar Target.Column kijk je dan naar ActiveCell.Column.

 

[Mswinkels]

Het is nu een event routine en dat kan uiteraard ook anders als je het met een knop wilt starten. Maar hoe moet het programma weten dat, wanneer de activecell een cell in Column 4 is dat hij dan naar de inhoud van D6 moet kijken? Voor het regelen van de upper- en lowercase ligt dat alleen daar vast dus dat mag vast geprogrammeerd worden.

Wat je denk ik bedoeld is dat wanneer de activecell een cell in Column 4 is hij dan de condities 1 t/m 8 allemaal moet uitvoeren. En hoe je dat kan doen zit dus in mijn voorbeeld. Of het een event routine is of achter een knop zit maakt daarbij niet uit. In plaats van te kijken naar Target.Column kijk je dan naar ActiveCell.Column.

Hallo edmoor.

Ik heb de private Sub nu aan elke sheet toegevoegd en het werkt prima.
Ik kreeg alleen een error als ik een cel leeg maak met delete of als ik de tekst weg haal doormiddel van backspace en dan enter.

Run-time error'-2147417848(80010108)':
Method'-_Default' of object 'Range' failed

Dit heb ik opgelost door in de Sub cellcasing een 'If' Statement na elke case te plaatsen.

Sub CellCasing(Cel As String, luCasing As String)
Select Case LCase(Range(luCasing).Value)
Case "upper case"
If Range(Cel) <> "" Then
Range(Cel) = UCase(Range(Cel))
Else
End If
End Select
End Sub

 

[edmoor]

Prima opgelost