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

*

http://www.theofficeexperts.com/forum/showthread.php?t=9308

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

*

http://www.excelforum.com/showthread.php?t=640108

PM Dave Hawley if you agree to forum rules.

PS This is not the first cross post

VBA Noob

Re: Delete Cells Based On Background Fill Color

See links below

e.g

http://www.ozgrid.com/forum/showthread.php?t=45580

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

Sub Hide_Rows()
Dim Wsht As Worksheet
Dim i As Integer
Application.ScreenUpdating = False


For Each Wsht In Worksheets
   With Wsht
        For i = 3 To 418
            Select Case .Range("b" & i).Value
                Case "2008-2", "2008-3", "2008-4", "2009-1", "2009-2", "2009-3", "2008-4"
                        .Rows(i & ":" & i).EntireRow.Hidden = True
            End Select
         Next i
    End With
Next Wsht


Application.ScreenUpdating = True
End Sub

VBA Noob

Re: Vba Set Range

pauldaddyadams,

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

*

http://www.excelforum.com/showthread.php?t=636090

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

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

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

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 ??

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

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

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

Re: Find The Friday Date Of The Month

See if links help

http://www.ozgrid.com/VBA/last-n-day-month.htm

http://www.ozgrid.com/forum/showthread.php?t=38981

http://www.ozgrid.com/forum/showthread.php?t=69223

VBA Noob