formule omzetten naar macro

[Rommyke]

Hoi allemaal

Ik heb onderstaande formule ingevuld in een cel C7 .

De formule zoekt via cell X2 (wat een datum is en een vaste plaats heeft) te samen met de waarde van B7

Via deze 2 waardes gaat hij in een ander excel file "pers.xls"gegevens ophalen en zet deze in C7, kan dit niet in een macro gebeuren en wat ik graag zou willen is dat als ik in een cell D2 een waarde "x" ingeef deze in cell C7 wordt gezet als de waarde die opgehaald is "0" is .Dit laatste zie ik niet zitten om nog eens in deze formule te zetten.

=IF(A7=0,"",IF($B$2=1,0,IF($B$2=2,HLOOKUP($X$2,'W:\test\[pers.xls]Sheet1'!$B$4:$IR$49,VLOOKUP(B7,'W:\test\[pers.xls]Sheet1'!$B$6:$IR$49,251,FALSE),FALSE),IF($B$2=3,HLOOKUP($X$2,'W:\test\[pers.xls]Sheet2'!$B$4:$IR$49,VLOOKUP(B7,'W:\test\[pers.xls]Sheet2!$B$6:$IR$49,251,FALSE),FALSE),IF($B$2=4,HLOOKUP($X$2'W:\test\[pers.xls]Sheet3'!$B$4:$IR$49,VLOOKUP(B7,'W:\test\[pers.xls]Sheet3'!$B$6:$IR$49,251,FALSE),FALSE))))))

 

[Demeter]

Ben er eachter dat de waarde in B2 een bladnummer is.
Maar wat wil je precies opzoeken?

Hierin ga zoeken naar de datum die in X2 staat? en als deze gevonden is wordt de celwaarde 251 plaatsen naar rechts genoteerd?

Post anders eens de twee voorbeeld files! Met bogus materiaal.

 

[brunniepoo]

neem aan kolom 251 van de matrix. Maar waarom staan overal komma's ipv puntkomma's? Of is dat standaard in de Engelse versie?

 

[Demeter]

weergave van komma's of puntkomma's ligt aan je windows settings deze kan je op internationaal zetten of nederlands, etc.
Je kan dus ook in een nederlandse versie van Excel met komma's werken (had tot voorkort dit probleem op kantoor )

 

[arafy]

Maar waarom staan overal komma's ipv puntkomma's? Of is dat standaard in de Engelse versie?

dit heeft meer met de landinstellingen te maken.
steven

 

[Rommyke]

Mannen ,
in bijlage een voorbeeld file zodat alles een beetje duidelijker wordt

Open bestand test

je kunt nu in B2 een selectie doen van een ploeg ( ploeg A, Ploeg B)

Afhangelijk van die ploeg krijg je een namen lijst .

Via deze namen lijst (volgnr) gaat hij in kolom C statussen bepalen die je in de file prognose vindt. Hierbij wordt tevens gekeken naar de datum (Cell K2 ) om de juiste gegevens over te halen .

Hoop dat het een beetje duidelijker is .


Zoals je kan zien in de file zou ik graag willen dat als je i cell D2 een X invult deze ook bij statussen komt te staan waar geen waarde is overgehaald .Als je nu manueel een X zet in kolom c overschijf je de formules.

Bedankt

 

[Rommyke]

.

Zoals je kan zien in de file zou ik graag willen dat als je i cell D2 een X invult deze ook bij statussen komt te staan waar geen waarde is overgehaald
Bedankt

Cell D2 moet natuurlijk in het voorbeeld D4 zijn

 

[Rommyke]

Iemand nog een idee ? ik geraak er niet uit

Bedankt

 

[Demeter]

Misschien niet de netse code maar probeer hem maar eens:

Sub overzetten()
Dim c As Range

Application.ScreenUpdating = False

naamblad = "Sheet" & Range("B4").Value
'datum = Format(Date, "d-mmm-yy")
'code werkt u wel met een hardcoded datum
datum = "29-apr-07"

Workbooks.Open Filename:="C:\Prognose.xls"

For Each c In Workbooks("TEst.xls").Sheets("Personen").Range("A7:A14")
    If c <> "" Then
         With Sheets(naamblad)
            Set rij = .Range("A2:A9").Find(c, LookIn:=xlValues)
            Set kolom = .Range("C1:AI1").Find(datum, LookIn:=xlValues)
            .Cells(rij.Row, kolom.Column).Copy c.Offset(, 2)
        End With
    End If
Next

ActiveWindow.Close
Windows("TEst.xls").Activate

Application.ScreenUpdating = True

End Sub

Verander wel even het pad naar je prognose file.

 

[Rommyke]

Hoi Ferenc ,

Al merciekes voor het meezoeken, jammer genoeg krijg ik de file niet aan het werk
Het begin heb ik al als volg veranderd

Sub overzetten()
Dim c As Range
Dim naamblad As String, datum As String
Dim rij As Integer, kolom As Integer

Application.ScreenUpdating = False

naamblad = "Sheet" & Range("B4").Value

datum = Range("k2").Value

nu krijg ik nog de fout bij Set rij= , object required .

With Sheets(naamblad)
            Set rij = .Range("A2:A9").Find(c, LookIn:=xlValues)
            Set kolom = .Range("C1:AI1").Find(datum, LookIn:=xlValues)
            .Cells(rij.Row, kolom.Column).Copy c.Offset(, 2)
        End With

In bijlage heb ik nog eens de 2 files gestoken.

Bedoeling is dus dat in bestand Test in kolom 'c' een waarde wordt overgenomen van een 2de file prognose. Afhangelijk van de datum in cell 'K2' en welke namen (in kolom A) of volgnr (in kolom B) moet er in de file 'prognose' de waarde gehaald worden voor deze zelfde periode .

Op het moment werkt het met een formule die in kolom C staat.Maar graag had ik dit in macro vorm.

Als je de datum veranderd (cell K2) zie je dat je andere statussen krijgt in kolom C

 

[Demeter]

Waar het voornamelijk in mijn code in zat is dat je met find lastig een datum kan vinden. Dit komt door het amerikaanse datum systeem binnen in Excel. Hier voor heb je dus een extra hulpje voor nodig in de trand van: Format(Date, "Short Date") en later in je zoekcode CDate. Extra info te vinden op Ozgrid : http://www.ozgrid.com/VBA/find-dates.htm

Let tevens op de tabbladnamen in je Prognose blad. Deze worden nu gezocht onder de namen Sheets1 etc. Ook enige aandacht is vereist bij het bepalen van je sheet nummer omdat je nu werkt met een inputbox waarin de 1e cel leeg is maar wel het getal 1 mee krijgt. Dit komt doordat dit de 1e keuze is in je lijst, vandaar dat er in de code nog een -1 staat achter Range(A4).Value.

De volgende code zou moeten werken.

Sub overzetten()
Dim c As Range
Dim naamblad As String, datum As String

Application.ScreenUpdating = False

naamblad = "Sheet" & Range("B4").Value - 1
datum = Format(Date, "Short Date")

Workbooks.Open Filename:="C:\Prognose.xls"

    For Each c In Workbooks("TEst.xls").Sheets("Personen").Range("A7:A14")
        If c <> "" Then
            With Sheets(naamblad)
                Set rij = .Range("A2:A9").Find(c, LookIn:=xlValues)
                Set kolom = .Range("1:1").Find(CDate(datum), LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
                .Cells(rij.Row, kolom.Column).Copy c.Offset(, 2)
            End With
        End If
    Next

ActiveWindow.Close
Windows("TEst.xls").Activate

Application.ScreenUpdating = True

End Sub

 

[Rommyke]

Hoi Ferenc ,

Voor de datum is het niet mogelijk om gewoon met een value te werken zoals onderstaand ?

datum = Range("k2").Value

Ik kreeg nog steeds de fout bij 'rij = ' variable is not defined

                Set [COLOR="Red"]rij =[/COLOR] .Range("A2:A9").Find(c, LookIn:=xlValues)
                Set kolom = .Range("1:1").Find(CDate(datum), LookIn:=xlFormulas,

bedank

 

[JanJantje]

Omdat het niet gedeclareerd is?
Probeer dit:

Dim rij as Range
Dim kolom as Range

 

[Rommyke]

Heb de code en file beetje aangepast.
En het werkt perfect !

Thnx mannen

naamblad = "Sheet" & Range("B4").Value - 1

heb ik vervangen door onderstaande code vermits ik telkens problemen had met de waarde van Range("B4") . Als ik ploeg B selecteerde zou ik waarde 2 moeten krijgen en ik kreeg telkens waarde 5 bij ploeg A werkte het dan weer prima

naamblad = Range("L5").Value

in L5 neem ik de waarde over van welke ploeg ( A of B )
en in prognose heb ik de sheets dan A en B genoemd

Sub Overzettentest()
Dim c As Range
Dim naamblad As String, datum As String
Dim rij As Range, kolom As Range

Application.ScreenUpdating = False

naamblad = Range("L5").Value
datum = Range("k2").Value


Workbooks.Open Filename:="C:\Prognose.xls"

    For Each c In Workbooks("TEst2.xls").Sheets("Personen").Range("A7:A14")
        If c <> "" Then
            With Sheets(naamblad)
                Set rij = .Range("A2:A9").Find(c, LookIn:=xlValues)
                Set kolom = .Range("1:1").Find(CDate(datum), LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
                .Cells(rij.Row, kolom.Column).Copy c.Offset(, 2)
            End With
        End If
    Next

ActiveWindow.Close
Windows("TEst2.xls").Activate

Application.ScreenUpdating = True

End Sub

bedankt Ferenc en JanJantje


Nog een kleine vraag,

is het mogelijk voor als hij niks heeft overgenomen er een X te plaatsen in de lege cellen in kolom c ?

 

[JanJantje]

Met deze functie is het mogelijk om lege cellen te vullen met X
Pas het bereik wel aan;

On Error Resume Next
     Range("C1:C10").SpecialCells(xlCellTypeBlanks) = "X"

Opgelet: de cellen moeten wel leeg zijn (dus ook geen formules bevatten)

 

[Rommyke]

Met deze functie is het mogelijk om lege cellen te vullen met X
Pas het bereik wel aan;

On Error Resume Next
     Range("C1:C10").SpecialCells(xlCellTypeBlanks) = "X"

Opgelet: de cellen moeten wel leeg zijn (dus ook geen formules bevatten)

Hoi JanJantje

Dit werkt perfect ...merciekes