Macro instead of a formula

[MSchädler]

I'm trying to create a macro to replace a formula and I don't seem to succeed. So I'm asking for your help.

Situation: I’m using a formula incolumn M, row 2 to 65536 which fetches the content of
column V, row 2 to 65536.
The result in column M, row 2 has tobe as “P0000000”. In this example = P0000001 (because V2 is 1)
The actual formula I use in column Mis: =TEXT(V2;"P0000000")

I need Help: I’m looking for a macroto calculate the same result instead of the formula above. The macro is to runas soon as a digit is entered in cell column V and displays the result incolumn M.

Reason: I believe that a macro will speedup the calculation time in my sheet.

Any suggestions:
M.

 

[MSchädler]

Re: Help needed for a macro instead of a formula

Hello Fluff
You are correct in your statement and the proposed VBA code works as asked in my test sheet.
What I forgot to tell is, that with the initial setup some row counts are not beeing used and therefore "forgotten". I somehow wanted to find these "Small" values in Column V and used a helping formula in W1 "[=SMALL(if(COUNTIF($V:$V,ROW($V$1:$V$10000))=0,ROW($V$1:$V$10000)),ROW(W2)-1)]" to do that. This is now my counter.
If you have a way to incorporate cell W1 as the counter in your VBA code this would be great.
You should see in the test sheet in cell W1 that formula (link drop box).

To your question going beyond V, my final sheet goes to column BC. Yet beyond column V the only command used is "clear contents". I imagine that I can copy your line "Range("W" & NxtRw).Resize(, 10).ClearContents", adapt "W" to the proper column, and insert it into your VBA code to clear additional cells from W to BC.

Again thanks for your great help and I would appreciate some more inputs.
Marc

 

[Fluff]

Re: Help needed for a macro instead of a formula

This will check W1 rather than col A for the number & will clear cols W to BC (you should check this, as I may have miscounted)

Private Sub CommandButton1_Click()

    Dim NxtRw As Long
    Dim MyCheck

Application.EnableEvents = False
Application.ScreenUpdating = False

    MyCheck = MsgBox("New project?", vbYesNo) 'show message (Ja/Nein)
    If MyCheck = vbNo Then Exit Sub
'
    NxtRw = Range("M" & Rows.Count).End(xlUp).Row + 1

    Rows(NxtRw - 1).Copy
    Rows(NxtRw).Insert
    Range("K" & NxtRw).Resize(, 10).ClearContents
    Range("N" & NxtRw).Value = Date
    Range("K" & NxtRw).Select
    Range("V" & NxtRw).Value = Range("W1").Value
    Range("M" & NxtRw).Value = Format(Range("V" & NxtRw).Value, "P0000000")
    Range("W" & NxtRw).Resize(, 33).ClearContents

Application.EnableEvents = True

End Sub

 

[MSchädler]

Re: Help needed for a macro instead of a formula

Hello Fluff

Great code and this makes my day! I tried it and it works great. Thank you a lot!
Marc

 

[Fluff]

Re: Help needed for a macro instead of a formula

Glad to help & thanks for the feedback

 

[MSchädler]

Re: Help needed for a macro instead of a formula

Hello Fluff
I hope your fine. I'm working on my sheet and I have one more question where I need your help. Perhaps you can give me some additional help with vba command lines.
In our Test sheet "Macro_Test_P000-3" and in register "Macro A" in column "O", I use a formula which, if in column T one of the several drop down entries are chosen, it goes and fetches from register "Auswahl" in column "C" the specific value.
I use a similar situation in register "Macro A" column "R". There the formula fetches from register "Auswahl" the contents of column "B".
Now I would like to replace the formulae in "O" and "R" and incorporate these in your macro with a command line(s).

Here is the link to the drop box: https://www.dropbox.com/s/wxfpaj84g987cr1/Macro_Test_P000-3.xlsm?dl=0

Do you have a suggestion?
Thanks in advance for your help.
Marc

 

[Fluff]

Re: Help needed for a macro instead of a formula

I've put one of the formulae in so that you can see how it's done & will leave you to do the other.

Private Sub CommandButton1_Click()

    Dim NxtRw As Long
    Dim MyCheck

Application.EnableEvents = False
Application.ScreenUpdating = False

    MyCheck = MsgBox("New project?", vbYesNo) 'show message (Ja/Nein)
    If MyCheck = vbNo Then Exit Sub
'
    NxtRw = Range("M" & Rows.Count).End(xlUp).Row + 1

    Rows(NxtRw - 1).Copy
    Rows(NxtRw).Insert
    Range("K" & NxtRw).Resize(, 10).ClearContents
    Range("N" & NxtRw).Value = Date
    Range("V" & NxtRw).Value = Range("W1").Value
    Range("M" & NxtRw).Value = Format(Range("V" & NxtRw).Value, "P0000000")
    Range("W" & NxtRw).Resize(, 33).ClearContents
    Range("O" & NxtRw).Formula =
    Range("R" & NxtRw).Formula = "=IF(Q" & NxtRw & "<>"""",VLOOKUP(Q" & NxtRw & ",Auswahl!A:B,2,FALSE),"""")"
    Range("K" & NxtRw).Select

Application.EnableEvents = True

End Sub

 

[MSchädler]

Re: Help needed for a macro instead of a formula

Hello Fluff
Thanks for all your efforts and I have imported your last VBA code in my test excel, adapted some lines and it works just fine!
Link: https://www.dropbox.com/s/iur7lwdvlpepwnb/Macro_Test_P000-31.xlsm?dl=0

Maybe my request was not clear enough, that I was looking as a result to get only values in the cells of columns “O” and “R” (see test excel in drop box). I left in column O2,3 and R2,3 the formulae that i use for reference.

The VBL code I’m looking should do the following:

For the first case, column “O”:

When one of the following cases from the drop-down list in column "T" (sheet Macro A)
#7_Beauftragung Rollout (PA genehmigt),
#8_Planung,
#9_ KV-Bearbeitung,
#10_Kreditfreigabe,
#11_Ausführung & Migration (BP genehmigt),
#12_Projektabschluss,
#13_Abgeschlossen
is picked, the logic is, to take the name from column “Q” and compare it with the names in sheet "Auswahl" column A. When a match is found, copy the adjacent value in column C and insert it as value in sheet Macro A in column “O”.
If no match is found we keep that cell empty.

For the second case, column “R”:

after creating a new row a name is entered in column “Q” and this name is compared with all the names in sheet “Auswahl” column “A”.
If a match is found, the adjacent value in column “B” is copied and inserted as value in sheet “Macro A” column “R”. If no match is found we keep that cell empty.

I would appreciate if you could help me with these tasks?
Thank you.
Marc

 

[Fluff]

Re: Help needed for a macro instead of a formula

If you want values rather than a formula, you'll need a new macro.
As this macro creates a new row, there will be nothing in either col Q or T & therefore no way of knowing what values to insert into Cols O & R.

 

[MSchädler]

Re: Help needed for a macro instead of a formula

If you want values rather than a formula, you'll need a new macro.
As this macro creates a new row, there will be nothing in either col Q or T & therefore no way of knowing what values to insert into Cols O & R.

Hello Fluff
If I understand you correctly, there can not be a VBL command to do this task. Therefore I continue using the formulae in colums O and R.

Thanks again for your help.
Marc

 

[Fluff]

Re: Help needed for a macro instead of a formula

It is possible to do, but you'd need a selection change event.
Which IMO would need to be a new thread