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.

 

[My Aswer Is This]

Re: Help needed for a macro instead of a formula

Try this:
This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the sheet tab
Select View Code from the pop-up context menu
Paste the code in the VBA edit window

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("V2:V" & Cells(Rows.Count, "V").End(xlUp).Row)) Is Nothing Then
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
Target.Offset(, -9).Value = "P0000000" & Target.Value
End If
End Sub

 

[MSchädler]

Re: Help needed for a macro instead of a formula

Hi,
First of all,many thanks for your help and I can tell you that your macro by itself works fine!

What Iforgot to tell is, that this command has to works together with some othermacro in my sheet. When I insert this in my existing sheet I get a debuggingerror message.
Therefore,I have to be more specific of what I want to achieve.
The sheetset up is as shown and I have an active-x button to start the copy/paste macro.It takes the last active row, copies it and pastes it on row below, clearingsome cell contents.

DB number

B

C

D

E

F

G

H

I

J

Process

Type (drop down)

Proj.ID

Date <tbody> </tbody>

ISP

C

Name

S

B

FS

R

Proj.ID

1

Initial

P0000001

29.05.2002

Olten-Zürich

OL-ZUE

#8_Planung

1

1

2

Initial

P0000002

11.12.2002

Bern-Wanzwil-Olten

BN-WANZ-OL

#8_Planung

1

2

3

Initial

P0000003

20.12.2002

Basel-Olten

BS-OL

#8_Planung

1

3

4

Initial

P0000004

20.12.2002

Sierre-Brig

SIE-BG

#8_Planung

1

4

5

Initial

P0000005

20.12.2002

Sierre-Brig_NFP01

SIE-BG

#8_Planung

1

5

6

Initial

P0000006

20.12.2002

Zofingen-Luzern

ZF-LZ

#8_Planung

1

6

7

Initial

P0000007

29.01.2003

Pratteln-Brugg-Wettingen

PR-WE

#8_Planung

1

7

8

Initial

P0000008

12.12.2003

Bellinzona-Locarno

BEL-LO

#8_Planung

1

8

9

Initial

P0000009

20.12.2003

Effretikon-Wetzikon

EF-WZ

#8_Planung

1

9

10

Initial

P0000010

20.12.2003

Frutigen-Raron (ATL)

ATL, KTU

#8_Planung

1

10

11

Initial

P0000011

20.12.2003

Seuzach-Winterthur Seen

W-RW, W-SH

#8_Planung

1

11

12

Initial

P0000012

20.12.2003

Thun-Frutigen-Brig (BLS)

TH-BG

#8_Planung

1

12

13

27.09.2017

1

<tbody>
</tbody>

<strike></strike>
What I want to achieve within the existing macro is to incorporate a command, which copies the value of cell A of the last active row and inserts that Value in column M in the format "P0000000".
Example (from the setup)
Copy row 14, paste into row 15, clear cells K15 to M and O to T15.
Now take value of Cell A15, copy value and paste value into V15 (normal) and in M15 with a format “P0000000”.

The macro I’m using to copy/paste, clear is as follow and works, except it does not put value in M15 (expected P0000013) or V15 (expected 13).

Private Sub CommandButton1_Click()
Application.EnableEvents = False
Application.ScreenUpdating = False
myCheck = MsgBox("New project?", vbYesNo) ‘show message (Ja/Nein)
If myCheck = vbNo Then Exit Sub
ActiveSheet.Range("M65536").End(xlUp).EntireRow.Select 'Copy last row in table
Selection.Copy
ActiveSheet.Range("M65536").End(xlUp).Offset(1, 0).EntireRow.Select
Selection.Insert 'paste one row below
Range("N" & (ActiveCell.Row)).Value = Date 'insert actual date
Intersect(Range("K:M,O:T"), ActiveCell.EntireRow).ClearContents 'delete cell contents
Application.ScreenUpdating = True
Application.EnableEvents = True
ActiveSheet.Range("M65536").End(xlUp).Offset(1, 0).EntireRow.Select
Range("K" & (ActiveCell.Row)).Select 'place cursor into cell K of active row
End Sub

Thanks for your much appreciated help.
M.
<strike></strike>

<strike></strike>

 

[My Aswer Is This]

Re: Help needed for a macro instead of a formula

I believe someone else here at Mr. Excel will have to help you. I wrote the script you asked for. Now your asking for a lot more. And showing me a script you already have which does lot's of things.

 

[MSchädler]

Re: Help needed for a macro instead of a formula

Ok.
Thank you for your help and sorry for that confusion.

Should I place a new thread?
M.

 

[Fluff]

Re: Help needed for a macro instead of a formula

Is this what you're after

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("A" & NxtRw).Value
    Range("M" & NxtRw).Value = Format(Range("V" & NxtRw).Value, "P0000000")

Application.EnableEvents = True

End Sub

 

[MSchädler]

Re: Help needed for a macro instead of a formula

Hi Fluff
Many thanks, this macro works quite well, except in column AI have an automatic row counter and I use the formula"=ZEILE()-1" for that. When you copy/paste you take that with you.

When you copy the last active row you paste it one below and that increments the counter in column A.
This is the value that I want to copy and insert in column"V" and as a specific format "P0000000" in column M. The expected value must be within the digit length (8 digits long).
In my sheet I use a cell formula to dothis =TEXT(V573;"P0000000") and that keeps any value within that length.

For example: I copy the full content of row 10.
A10 in this casehas the formula =Zeile()-1
This gets pasted on row 11 with all formulae.
The specific result I'm looking for is to have in row 11 column M the value "P0000011" and in column V the value "11".


I send you the link to my drop box with the test excel. This has my macro and might help.
https://www.dropbox.com/s/y6y0adcq5puzhmb/Test_Macro_P00.xlsm?dl=0
_{^{<strike>
</strike>}}
Thank you for your help!

Marc

 

[MSchädler]

Re: Help needed for a macro instead of a formula

Hello Fluff
In the mean time I've come up with the following vba. I'm sure it is not the best but for now it does the job. Maybe you can help to make it mor efficient?

This is the vba command:
Private Sub CommandButton3_Click()
Application.EnableEvents = False

myCheck = MsgBox("Create new project?", vbYesNo) 'Show message with (Yes/No))
If myCheck = vbNo Then Exit Sub
Application.ScreenUpdating = False 'do not update screen while processing
ActiveSheet.Range("M65536").End(xlUp).EntireRow.Select 'copy last row of column M
ActiveSheet.Range("M65536").End(xlUp).Offset(1, 0).EntireRow.Select
Selection.Insert 'paste one below last active row
ActiveSheet.Range("W1").Select 'fetch the value in W1. In W1 I have the formula:[=SMALL(if(COUNTIF($V:$V,ROW($V$1:$V$10000))=0,ROW($V$1:$V$10000)),ROW(W2)-1)] which find the smallest unused value in the counter.
Selection.Copy
ActiveSheet.Range("V65536").End(xlUp).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False


Range("N" & (ActiveCell.Row)).Value = Date 'set actual date
Intersect(Range("K:L,P:Q,S:T,AA:BC"), ActiveCell.EntireRow).ClearContents 'clear contents of cells in row
Application.ScreenUpdating = True
Application.EnableEvents = True

Range("K" & (ActiveCell.Row)).Select 'set cursor to front of row
End Sub

 

[MSchädler]

Re: Help needed for a macro instead of a formula

Here is the link to my drop box with the test sheet.
https://www.dropbox.com/s/dodz8e3zrycae89/Macro_Test_P000-1.xlsm?dl=0
M

 

[Fluff]

Re: Help needed for a macro instead of a formula

I have just tested the code I supplied in post#6 on your file & it does just what you're asking for.
That said, your new code (in post#8) is working on columns beyond V, but there is nothing in those columns on your test sheet.
You need to explain why the code I supplied does not work. Or I cannot help any further.