# Posts by VBA Noob

Re: Parameter Query Conversion Error

Thanks NBVC

Nasim,

MODERATOR NOTICE: This topic has also been posted on other sites and may already have an answer elsewhere. Please take this into consideration when answering this question

*

PM Dave Hawley if you agree to the rules

VBA Noob

Re: Differentiate Vba Message Box Response

Or see the Possible Answers suggestions above.

VBA Noob

Re: Select Row By Specific Value Then Copy To Another Sheet

Thanks StephenR,

cleaco,

MODERATOR NOTICE: This topic has also been posted on other sites and may already have an answer elsewhere. Please take this into consideration when answering this question

*

PM Dave Hawley if you agree to the rules

VBA Noob

Re: Summary Report Of Shares Bought And Sold

Thanks TheDude,

MODERATOR NOTICE: This topic has also been posted on other sites and may already have an answer elsewhere. Please take this into consideration when answering this question

*

VBA Noob

Re: Return Most Frequent Bin Of Numbers

hankach,

MODERATOR NOTICE: This topic has also been posted on other sites and may already have an answer elsewhere. Please take this into consideration when answering this question

*

PM Dave Hawley if you agree to the rules

Re: Unhide/Hide Sheet Based On Value In Cell

caliskier,

MODERATOR NOTICE: This topic has also been posted on other sites and may already have an answer elsewhere. Please take this into consideration when answering this question

*

PM Dave Hawley if you agree to forum rules.

PS This is not the first cross post

VBA Noob

Re: Extract Top Five Maximum Values From A Range

This assumes the List items in Col A and No's in Col B

Quote

=INDEX(\$A\$1:\$A\$7,MATCH(LARGE(\$B\$1:\$B\$7-ROW(\$B\$1:\$B\$7)/10^5,ROW(A1)),\$B\$1:\$B\$7-ROW(\$B\$1:\$B\$7)/10^5,0))

It's an array formula (Enter with Ctrl + Shift + Enter) Drag down formula for 2nd, 3rd etc

VBA Noob

Re: First Name-last Name To Last Name-first Name

No problem.

Try

Quote

=IF(LEN(A1)-LEN(SUBSTITUTE(A1,".",""))=3,SUBSTITUTE(TRIM(MID(A1,FIND(".",A1)+1,FIND(",",A1))),"M.D.","")&LEFT(A1,FIND(".",A1)),SUBSTITUTE(TRIM(RIGHT(A1,LEN(A1)-FIND(" ",A1)))," M.D.","")&" "&LEFT(A1,FIND(" ",A1)))

VBA Noob

Re: First Name-last Name To Last Name-first Name

Maybe

Quote

=IF(LEN(A1)-LEN(SUBSTITUTE(A1,".",""))=3,SUBSTITUTE(TRIM(MID(A1,FIND(".",A1)+1,FIND(",",A1))),"M.D.","")&LEFT(A1,FIND(".",A1)),SUBSTITUTE(TRIM(RIGHT(A1,LEN(A1)-FIND(" ",A1)))," M.D.","")&LEFT(A1,FIND(" ",A1)))

VBA Noob

Re: First Name-last Name To Last Name-first Name

See how you fair with this formula first as it sounds like you have lots of variables so it may not work for them all.

Quote

=SUBSTITUTE(TRIM(MID(A1,FIND(".",A1)+1,FIND(",",A1))),"M.D.","")&LEFT(A1,FIND(".",A1))

VBA Noob

Re: First Name-last Name To Last Name-first Name

OverKnight,

A couple of questions first

Is the seperator a "-" or a gap ??

Are you after code or a formula.

Do any names have a middle name

A few examples would help

VBA Noob

Re: Condense Hide Rows Vba Code

Maybe

VBA Noob

Re: Vba Set Range

MODERATOR NOTICE: This topic has also been posted on other sites and may already have an answer elsewhere. Please take this into consideration when answering this question

*

PM Dave Hawley if you agree to the rules.

Dave, I doubt that he will as he didn't follow them at excel forum either

VBA Noob

Re: Inputbox Code To Get Uk Date

Maybe

Code
``````Dim dt As Date

dt = Application.InputBox("Enter a date")
dt = Format(dt, "dd/mmm/yyyy")

Worksheets("Choose").Range("c1").Value  = dt``````

Re: Inputbox Code To Get Uk Date

Try

Code
``````Dim dt As Date

dt = Application.InputBox("Enter a date")

MsgBox Format(dt, "dd mmm yyyy")``````

VBA Noob

Re: Adding Alternate Values In Vba

You don't need a macro.

Try

Quote

=SUMPRODUCT((MOD(ROW(INDIRECT("1:"&ROWS(A1:A7))),3)=1)*A1:A7)

VBA Noob[hr]*[/hr] Auto Merged Post Until 24 Hrs Passes;[dl]*[/dl]If you must have code you could try

Code
``````Dim LastRow As Long, i As Long, X as long
LastRow = Cells(Rows.Count, "A").End(xlUp).Row

For i = 1 To LastRow Step 3
X = X + Cells(i, "A").Value
Next i
MsgBox X``````

VBA Noob

Re: Vba Transpose Without Pastespecial

What's wrong with transpose ??

Code
``````Dim Rng As Range
Set Rng = Range("A1:A10")
Rng.Copy
Range("O50").PasteSpecial Paste:=xlValues, Transpose:=True``````

VBA Noob

Re: Disable Changing R1c1 Style To A1 Style

You can grey out Tools > options tab for the workbook. Place this code in ThisWorkbook and save the workbook. When the user opens it the option is greyed out and when they close the workbook the option is available again

Code
``````Private Sub Workbook_Open()
Dim Opt As Object
Dim TB As Object

Set TB = Application.CommandBars("Worksheet Menu Bar").Controls("Tools")
Set Opt = TB.Controls("Options...")
Opt.Enabled = False

End Sub``````

Code
``````Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim Opt As Object
Dim TB As Object

Set TB = Application.CommandBars("Worksheet Menu Bar").Controls("Tools")
Set Opt = TB.Controls("Options...")
Opt.Enabled = True

End Sub``````

VBA Noob