Free Microsoft Excel 2013 Quick Reference

Clean Trim Proper Function Using a Macro

Hi Everybody,

I regularly work on large amount of data in excel for which I do the clean up everytime.
I generally use Clean,Trim and Proper functions.

Could someone please help me out with a macro which should do all the three activities with a click of button.

For Example If Column A has data for first ten thousand rows and when I select the data and run the macro all the three functions should work on that and moreover then same action is done on column B it should work Vice versa.


Post your answer or comment

comments powered by Disqus
Greetings,

I cannot figure out how to use the Proper Function in a macro that runs
my spreadsheet. I need the out put to be normal and not all caps. The
users, use all caps because they are too lazy to hit the shift key.

I have 12 sheets, one for each month with 12 columns and 200 rows per
sheet. I know from reading the other "proper, tags" in the forum the
entire book can be done with a macro, at the location I try to place
it, everything after the proper, stops working. Any assistance would be
appreciated!

Thanks,

--
jackel

Hello,

I wanted to do the equivalent of Clean(Trim(.)) on a list of cells from A1:A1000 using a macro. Does someone have an idea of how to achieve that?

thanks in advance,
Andy

I have a point in a macro where the macro goes down a column examining each cell. Is there a way that at each cell it inspects, it can render the text with a title appearance? If this were simple a worksheet without automation, a user would enter the title in cell A1 (a sad story) and then in cell B1 I would type =proper(A1) and the corrected title would appear in B1 as a result (A Sad Story). But I am not sure how I can get the macro to go down the column and look for and make this same type of correction in the existing cell. Do any of you?

Hi all

I've been tinkering with ways to include the LEFT function in a macro, but without success.

I'm trying to isolate the first 2 characters in an a cell, and then use that to go to a separate worksheet with the same 2 character name. For instance:

Cell A1 is "AD1234". I want the macro to recognise the "AD" part and go to a worksheet called "AD" and do some stuff (lookups etc).

I thought it would be quite easy but I'm absolutely stumped. I'm going through Excel VBA For Dummies now, but I get the feeling I'm a level below dummy cos I'm just not getting it. Is what I'm trying to do possible?

Any pointers much appreciated, as always.

Thanks
Darren

When using the "mailto" function in a macro, it allows me enter the
receipient and subject, but I'm not sure of the coding used to enter text
into the body of the email, in short I need to email the link for the
location of the spreadsheet for the user to click on in the email so it
automatically opens.

cheers,
Mike

Hi,

I got some kind of a standard problem but could not find any solution for it up to now. Here the setting: I want to program a VBA function (not a macro!). The function should optimize some expression depending on its input parameters (a, b, and c) by using the Solver (e.g. I want to call Solver for getting a solution rather than programing some iterations myself). E.g. something like the following will be required:

Function SolverInVBA(a, b, c)
x = 0 'some dummy as starting point for the optimization
y = a * x^2 + b * x + c 'just some stupid example
SolverReset
-----------------Here comes the problem-----------------
SolverOk SetCell:="y"(???), MaxMinVal:=2, ByChange:="x"(???)
SolverSolve UserFinish:=True
SolverFinish KeepFinal:=1
SolverInVBA = "Solution of Solver"(???)
---------------------------------------------------------
End Function

The points where I have problems are marked with "(???)". In addition, how do I tell the function to update once one of its input parameters has changed? Has anybody an idea how to implement this?

Best,
Felix

Hello,
I do not know either to use a macro or a function program. I am using couple of sheets in one worksheet. On the first sheet I plan to ask the user to select two data’s from both drops-down menus. The other two data will be inserted by the user in a particular cell that I have named. The third data is in sheet two as a table which I will need the information given above to find the numeric data from the table.
My plan was hopefully use a button called "Calculate" so when the user is done selecting and inputting he or she will click on the "Calculate" button and this function program will enable and conclude.
This program will need to get the data inputted from the user on the first sheet. I know how to do this, I even done the button without the function. I need help using the data given by the user to find the numeric data in the table in sheet two. I need help getting started with the program using the above information to look for the numeric info in the table.

I am trying to use the Vlookup function in a macro but I can't figure out how to write it. I am using a controlled loop to cycle through my data. So for the first entry, my lookup data is in cell A2. The lookup table is located in a sheet called 'Trade Table' and the lookup field is in Column B and value needed is in column C. The results go in column G.

The excel formula is as follows:
I have the macro code started, but can't figure out how to finish it (at least I think it is started correctly)
        For i = 1 To n
            Range("G" & i + 1).Value = Application.WorksheetFunction.VLookup(.Range("A & i + 1"),

        Next i


Is there a way to import info from a website using a macro function? and if
so, how is this process done.

When using the "mailto" function in a macro, it allows me enter the
receipient and subject, but I'm not sure of the coding used to enter text
into the body of the email, in short I need to email the link for the
location of the spreadsheet for the user to click on in the email so it
automatically opens.

cheers,
Mike

Hi all,

I have problems using a macro to define the function of let's say cell A5.

I want to put the following function in the cell A5. I have tested this function itself and it works

=IF(ISERROR(VLOOKUP($A4;INDIRECT($C$3);1;FALSE));" ";VLOOKUP($A4;INDIRECT($C$3&"2");2;FALSE))

I use the following macro, but I get the runtime error 1004 (Application-defined or object defined error)

Sub Button18_Click()

Range("c4").FormulaR1C1 = "=IF(ISERROR(VLOOKUP($A4;INDIRECT($C$3);1;FALSE));""
"";VLOOKUP($A4;INDIRECT($C$3&""2"");2;FALSE))"

End Sub
I really don't understand what I'm doing wrong

Thank you all,
-Juhanen-

Hi all,

I want to use a macro which unlocks couple cells in a function which is typed to a cell. Example of simplified version of my problem

My macro which is typed in sheet 1 code
 
Private Sub macro1()

ActiveSheet.Unprotect
'Sheets("sheet1").Range("C4:C200").Locked = False
'ActiveSheet.Protect

End Sub
And the function which is typed to the cell D4: (but this doesn't work, maybe because of the way I refer to the macro?)

If(C3="car";macro1();"fail"

So what did I do wrong?

thank you all,
Juhanen

Hello!

I want to do the following using VBA:

I want to call on a function with a macro/button in the same way as when you choose "insert-function" but in a way that the user himself is able to put in values on the spredsheet.

Anyone who can help me on this? I would appreciate it!

I would like to use a macro to perform two specific functions:

1) Link data from a .txt (or .xls) file to a specific cell. I can do this using a hard link, but cannot figure out how to do it using a macro.

2) I would like to be able to link to variable folders, that is, have it link to a particular file regardless of where the file is located. Is this possible? Again, using hard links I do not know of a way to do this.

My ultimate goal is to have a macro that links to a particular file located in the current folder I am working in. For example, I like to use a single .xls file to analyze data in many different folders, and these folders are most likely not be the folder the parent .xls file is located in.

I recently, found that I needed a mechanism to count the number of rows that
existed in each worksheet.

I discovered that the COUNTROWS functionality was just what I wanted.

I decided that I would insert the functionality into a macro, which is used
on a quarterly basis that basically, merges three sheets, then creates
individual sheets for each company and finally undertakes Page formatting.

Within the format macro I inserted the following:- (thank you to Dave Ramage)

Sub DoStuff()
Dim wks as Worksheet
Dim lLastRow as Long

For Each wks in Activeworkbook.Worksheets
lLastRow = wks.Range("A1").End(xlDown).Row
wks.PageSetup.PrintArea = "$A$1:$K$" & lLastRow
wks.Cells(lLastRow + 1, 1).Formula = "TOTAL"
wks.Cells(lLastRow + 1, 2).Formula = "=COUNTROWS(A:A) – 2"
‘the –2 at the end is to discard one header line, and the countrows line
Next wks
End Sub

Unfortunately when I run it, I get a ‘Run-time error 1004 –
Application-defined or object-defined error’.

I have had a look at it, and word TOTAL is inserted in the last row in
Column A for the very first sheet only. It then gives the above error. The
line it seems to complain about is wks.Cells(lLastRow + 1, 2).Formula =
"=COUNTROWS(A:A) – 2"

Any suggestions would be most welcome

Regards

Pank

Hi,

I am using a loop to send an email to each relevant person from a range of email addresses. Each time the loop runs a temporary file is created, which I then save afterwards which a unique name for each individual. This was working fine until I attempted to amend the RangetoHTML function to use a macro enabled template rather than just an ordinary workbook. Now the email generated is blank and I can't understand why.


	VB:
	
 Range) 
     ' Changed by Ron de Bruin 28-Oct-2006
     ' Working in Office 2000-2010
    Dim fso As Object 
    Dim ts As Object 
    Dim TempFile As String 
    Dim TempWB As Workbook 
     
    TempFile = Environ$("temp") & "/" & Format(Now, "dd-mm-yy h-mm-ss") & ".htm" 
     
     'Copy the range and create a new workbook to past the data in
    Application.DisplayAlerts = False 
    rng.Copy 
    [B]Set TempWB =
Workbooks.Add(template:="[URL="file://dartgroup.plcdata1Jet2FlightOperationsJet2FltOpsEFBChange"]dartgroup.plcdata1Jet2FlightOperationsJet2FltOpsEFBChange[/URL]
RequestsChangeTemplate.xltm") 
    [/B]    With TempWB.Worksheets("Sheet1") 
    .Cells(2, 1).PasteSpecial Paste:=8 
    .Cells(2, 1).PasteSpecial xlPasteValues, , False, False 
    .Cells(2, 1).PasteSpecial xlPasteFormats, True, False, False 
    .Cells(2, 1).Select 
    Application.CutCopyMode = False 
    On Error Resume Next 
    .DrawingObjects.Visible = True 
    .DrawingObjects.Delete 
    On Error Goto 0 
End With 
Application.DisplayAlerts = True 
 
 
 'Publish the sheet to a htm file
With TempWB.PublishObjects.Add( _ 
    SourceType:=xlSourceRange, _ 
    Filename:=TempFile, _ 
    Sheet:=TempWB.Worksheets("Sheet1").Name, _ 
    Source:=TempWB.Worksheets("Sheet1").UsedRange.Address, _ 
    HtmlType:=xlHtmlStatic) 
    .Publish (True) 
End With 
 
 'Read all data from the htm file into RangetoHTML
Set fso = CreateObject("Scripting.FileSystemObject") 
Set ts = fso.GetFile(TempFile).OpenAsTextStream(1, -2) 
RangetoHTML = ts.ReadAll 
ts.Close 
RangetoHTML = Replace(RangetoHTML, "align=center x:publishsource=", _ 
"align=left x:publishsource=") 
 
 'Define the unique file name and save the document in the user's folder.
Dim TempSaveAs As String 
 
TempSaveAs =
"[URL="file://dartgroup.plc/data1/Jet2FlightOperations/Jet2FltOps/EFB/Change"]dartgroup.plcdata1Jet2FlightOperationsJet2FltOpsEFBChange[/URL]
Requests" & Me.txtcvalue.Value & "" & "EFB" & Me.txtConfigNo.Value & " " & Me.txtTitle.Value 
 
 
TempWB.SaveAs (TempSaveAs) 
TempWB.Close 
 
 
 'Delete the htm file we used in this function
Kill TempFile 
 
Set ts = Nothing 
Set fso = Nothing 
Set TempWB = Nothing 
End Function 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
I have changed a few bits and pieces from Ron de Bruin's original code, but it is using the template (in bold) which has caused it to stop working. The debugger doesn't highlight any lines, it just doesn't populate the body of the email.

This is the code I am using for the email creation:


	VB:
	
 
Dim OutMail As Object 
Dim cell As Range 
Dim rng As Range 
Dim CellRng As Range 
Dim cvalue As String 
Dim TempHyperlink As String 
 
Application.ScreenUpdating = False 
Set OutApp = CreateObject("Outlook.Application") 
Set rng = TempSheet.Rows("1:16") 
 
With ThisWorkbook.Worksheets("Log") 
    Set CellRng = .Range(.Cells(iRow, 6), .Cells(iRow, 26)) 
End With 
 
On Error Goto cleanup 
For Each cell In CellRng 
     
     
     
    If cell.Value Like "?*@?*.?*" Then 
        txtcvalue.Value = Left(cell.Value, WorksheetFunction.Search("@", cell.Value, 1) - 1) 
        TempHyperlink =
"[URL="file://dartgroup.plcdata1Jet2FlightOperationsJet2FltOpsEFBChange"]dartgroup.plcdata1Jet2FlightOperationsJet2FltOpsEFBChange[/URL]
Requests" & Me.txtcvalue.Value 
        Set OutMail = OutApp.CreateItem(0) 
        On Error Resume Next 
        With OutMail 
            .To = cell.Value & vbNewLine 
            .Subject = Me.txtTitle.Value 
            .ToDoTaskOrdinal = DeadlineDate 
            .TaskDueDate = DeadlineDate 
            .TaskStartDate = Me.txtIssueDate.Value 
            .FlagStatus = 2 
            .FlagRequest = strFlagRequest 
            .FlagIcon = 6 
            .HTMLBody = EmailContent & "    Record  - Please complete the relevant Department Specific Information Fields " &
RangetoHTML(rng) & "
" & "
" & "
" & "Should you require further information regarding the Change Request prior to providing this feedback, please see" & Me.txtOriginator.Value & " prior to the deadline." .Send End With On Error Goto 0 Set OutMail = Nothing End If Next cell cleanup: Set OutApp = Nothing
If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Thanks,

Maria.

Hi,

I have a workbook contining a master spreadsheet, whenever a new row is
created in the master sheet I want to insert it into a specific (but
changing) row in one of the other spreadsheets in the workbook
depending on a value being entered in a specific column in the master
sheet.

I have used a macro with an autofilter function to do this, but it
overwrites data in the row I specify to copy to, is their any way I can
make it add the data to this row and automatically move all the other
data that follows down a row. I ame using the range.copy function

I want to add information to a worksheet and then sort the data into
alphabetical order all using a macro.

Can not seem to be able to get this function to work.

Can it be done? If so how?

when using an IF then else function inside a macro, how do you reference a
cell for a specifc value in the IF function. Any help would be greatly
appreciated.

Thanks
--
Sandi Gardner

I am trying to utilise the 'find' function in a macro activated by a button. I've tried recording the actions but this does not seem to work. ny help would be gretly appreciated.

Regards
Glenn

I recently, found that I needed a mechanism to count the number of rows that
existed in each worksheet.

I discovered that the COUNTROWS functionality was just what I wanted.

I decided that I would insert the functionality into a macro, which is used
on a quarterly basis that basically, merges three sheets, then creates
individual sheets for each company and finally undertakes Page formatting.

Within the format macro I inserted the following:- (thank you to Dave Ramage)

Sub DoStuff()
Dim wks as Worksheet
Dim lLastRow as Long

For Each wks in Activeworkbook.Worksheets
lLastRow = wks.Range("A1").End(xlDown).Row
wks.PageSetup.PrintArea = "$A$1:$K$" & lLastRow
wks.Cells(lLastRow + 1, 1).Formula = "TOTAL"
wks.Cells(lLastRow + 1, 2).Formula = "=COUNTROWS(A:A) – 2"
‘the –2 at the end is to discard one header line, and the countrows line
Next wks
End Sub

Unfortunately when I run it, I get a ‘Run-time error 1004 –
Application-defined or object-defined error’.

I have had a look at it, and word TOTAL is inserted in the last row in
Column A for the very first sheet only. It then gives the above error. The
line it seems to complain about is wks.Cells(lLastRow + 1, 2).Formula =
"=COUNTROWS(A:A) – 2"

Any suggestions would be most welcome

Regards

Pank

Hey all,

What I am trying to do is to program a Macro to, once a quarter, go in and change the date of the file a VLOOKUP is using. A longer explanation would be that I am using VLOOKUP values to pull together various data. The files are named the same every month and only the date changes. I was wondering if I could use a macros to change the dates of the file names the VLOOKUP is using.

First does anyone know if a macro can do this sort of thing and if not if anyone has any suggestions on a better method or better use of excels functions. Any help would be great. Thanks all!

I'm trying to use these worksheet functions in a macro and I've tried using:

Dim Year as Integer
Year = Application.WorksheetFunction.Year(Range("S3"))
where S3 contains the date such as 6/28/2010.

However, this gives me a object does not support this method error. It does not work for any of the other listed functions either although it does work for the Weekday function... Anyone know how I might be able to use these functions in a macro?

Thanks.

I have 50 worksheets in a single workbook which all pull data from the same
source document. Being that I need to perform this function monthly, is
there any way to automate the filtering process using a macro?


No luck finding an answer? You could always try Google.