MACRO HELP

[Gaurang Mhatre]

Hi Friends,

I have this data in excel sheet and i want to run macro on it.
What i want to do is whenever i put name in column A, rest details should run automatically with the help of the formula.
But, now the thing is my boss needs only in macro even after giving him the sheet with formulas which automatically captures require details in other columns once you enter in Column A. but, still he needs a macro to be run on it.

Attached is the dummy data.
Can you guys please help me how to put macro for such case, if i input name in column A rest details should come automatically, also i have put formulas for your understanding i.e what i am trying to capture , which is highlighted in blue color.

Thanks in advance for your help.

Regards,
Gaurang.

 

[Hui]

Why not lock the sheet and hide formulas
Your boss won't know any difference

 

[Gaurang Mhatre]

Hui,

Lol...
But, the problem is i cant edit or add if the sheet is protected since there will be entries on daily basis. is macro possible.?
i dont see any difference between the formula i used and macro both will give same result after all but still my boss wants macro...lol

Anyways thanks for your reply hui!

Regards,
Gaurang.

 

[Somendra Misra]

Hi Gaurang,

See the attached file. Is this what you are after.

Regards,

 

[Gaurang Mhatre]

Thanks somendra...i was looking for this only..

You saved me !!

God bless u

Regards,
Gaurang

 

[Somendra Misra]

Glad you got it. Thanks for your kind word and welcome back anytime.

Regards,

 

[Gaurang Mhatre]

Hey somendra,
Just a small doubt, you have used
"For i = 2 To lastrow"
what is this i for??
Can you explain coding, if possible.

Thanks!
Regards,
Gaurang

 

[Somendra Misra]

@Gaurang Mhatre

Lastrow is a variable which will store the last row with data in column A of this sheet.

Now i is a loop variable which goes from row 2 to last row in this case 6 because your formulas has to apply on this data. If you insert more name , mobile # and amount and press run button it will execute for all rows.

Rest codes are simple formula which were there in your sheet.

Regards,

 

[Gaurang Mhatre]

Thanks somendra...got it!

Regards,
Gaurang

 

[Hui]

To use Formulas
You unprotect Column A
Protect Columns B:Z (Where the formulas are)
Add enough formulas to Row 1000 etc
Protect the Worksheet and Hide Formulas
Voila
A person can add records in Column A and results will go to columns B:Z
But no visibility of formulas
You can even password protect it

 

[Gaurang Mhatre]

Thanks Hui. vl use this trick as well...

Regards,
Gaurang.

 

[ashish mehra]

Dear Luke & SM,

Amazing stuff, to add on what if my name contains "mhatre, gaurang".

Regards,
AM

 

[Somendra Misra]

@ashish mehra

You mean to say in sample file the names are like last name,first name and still you want to extract last name as earlier?

One more questions--- are all names listed in this pattern or some in this and some in like the last one?

Regards,

 

[ashish mehra]

Hi SM,

Yes, I want to extract Last Name as "Mhatre" separated with comma.

Regards,
AM

 

[Somendra Misra]

Just read my edited comment #13.

Regards,

 

[ashish mehra]

Dear SM,

Column Name will contain name as "Gaurang Mhatre" and "Mhatre, Gaurang".
Output required: "Mhatre" in all scenarios.

Regards,
AM

 

[Somendra Misra]

Just see the attached file.

Regards,

 

[ashish mehra]

Hey SM you are Champ!!!!!!!!!!!!!

Just see the attached file.

Regards,

 

[Somendra Misra]

@ashish mehra

Thanks for your kind words, but I am still at begginer level in VBA.

Regards,

 

[rumshar]

Hi,
Why not have this macro work automatically whenever there is change in column A?..
Your boss doesn't have to click button also...

like...
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 Then
Dim ws As Worksheet

Set ws = Worksheets("Sheet1")

lastrow = ws.Range("A" & Rows.Count).End(xlUp).Row

For i = 2 To lastrow
Cells(i, 4) = Rnd()
Cells(i, 5) = Mid(Cells(i, 1).Value, InStr(Cells(i, 1), " "), Len(Cells(i, 1)) - InStr(Cells(i, 1), " ") + 1)
Cells(i, 6) = Left(Cells(i, 2).Value, 5)
Cells(i, 7) = Left(Cells(i, 4).Value * 1000000, 4)
Cells(i, 8) = Cells(i, 5) & Cells(i, 6) & Cells(i, 7)

Next i
End If
End Sub

With Regards
Rudra

 

[SirJB7]

Hi, Gaurang Mhatre!

You have many options in the menu, just choose that which you think it might suit better for your job:

a) Somendra Misra's: for a lot of rows it'd be slower than c), despite it should be run again if any value in columns A:B changes as it stores fixed values (even that of RAND).

b) rumshar's: solves partially the update problem (it should includes column B too), but it runs a bunch of times on the same cells had they been changed or not, i.e., doesn't circumscribe to Target range and operates on all column A.

c) Somendra Misra's with formulas: much faster for any number of rows as it avoids the loop cell by cell, solves update solution but not additions.

Sub populate_values()

Dim ws As Worksheet

Set ws = Worksheets("Sheet1")

LASTROW = ws.Range("A" & Rows.Count).End(xlUp).Row

Cells(2, 4).Formula = "=RAND()"
Cells(2, 5).Formula = "=MID(A2,SEARCH("" "",A2),LEN(A2)-SEARCH("" "",A2)+1)"
Cells(2, 6).Formula = "=LEFT(B2,5)"
Cells(2, 7).Formula = "=LEFT(D2*1000000,4)"
Cells(2, 8).Formula = "=E2&F2&G2"
Range("D2:H2").Copy
Range("D2:H" & LASTROW).PasteSpecial xlPasteFormulas
  
End Sub

d) Convert your range into structured table and forget about formulas: just set them in 1st data row (2)... et voilà.

Regards!