Over 1 million tech questions and answers.

Solved: EXCEL: Is ScreenUpdating=False working im my macro?

Q: Solved: EXCEL: Is ScreenUpdating=False working im my macro?

Hi

I started to use VBA recently. I have a macro, which use solver to get some solution. The worksheet contains about 14,000 rows of data and I need to use solver as many times. In the macro, I added Application.ScreenUpdating=False at the beginning and Application.ScreenUpdating=True at the end. But it seems to me that the screen kept updating. I can see the cells changing values one by one. It took more than one and a half hour to finish the macro. I guess the Application.ScreenUpdating=False doesn't really work in my macro. But I couldn't figure out the reason. Any help or suggestions are greatly appreciated.

Since my data is pretty large, I created a "vbtry.xls' and write the macro in a simple manner. But it uses the solver repeatedly. The macro name is "test". As you can see, the screen keeps flashing if you run the macro. My understanding is that: with Application.ScreenUpdating=False, the window should look like freezing and the data should be posted all at once at the end. Am I right on this? What's wrong with my macro?

Please see the attached file. Thanks.

RELEVANCY SCORE 200
Preferred Solution: Solved: EXCEL: Is ScreenUpdating=False working im my macro?

I recommend downloading and running Reimage. It's a computer repair tool that has been proven to identify and fix many Windows problems with a high level of success.

I've used it in the past to identify and fix everything from blue screens (BSOD's), ActiveX errors, corrupt files and processes, dll/exe/sys errors, recover lost memory, Windows update problems, defragging, malware removal etc.

You can download it direct from this link http://downloadreimage.com/download.php. (This link will automatically start a download of Reimage that you can save to your computer.)

A: Solved: EXCEL: Is ScreenUpdating=False working im my macro?

In your example you have not defined SolverSolve, Solverfinish, and SolverOK. When I run the macro the code bugs out. Are you missing some code? Explain how the macro is supposed to work and what these variables or procedures are. Is there an add-in missing from the project?

Regards,
Rollin

Read other 3 answers
RELEVANCY SCORE 94

Is there something wrong with my excel program, settings?

Application.ScreenUpdating = False
dc = range("cb77") 'does not kill it
range("cc77") = dc 'this is a killer

range("cc77") = range("cb77") 'this kills it too
MsgBox Application.ScreenUpdating & "3" '------------------ <view result

Between them they are murdering my program which is infested with
with this sort of coding (in my ignorance I used them a lot)
Is there any way to stop the screen updating by re-writing the code or something?
 

A:Solved: screenupdating false killer

Read other 8 answers
RELEVANCY SCORE 72.4

Hi All

I have a trial excel 2007 version that came with my Vista Home Premium.
I had a spreadsheet which contains macros and I had no problems running with XP.

I changed some of the settings and completed the following:
http://www.howtogeek.com/howto/wind...nt-control-uac-the-easy-way-on-windows-vista/

I followed the following link from the developer of my particular spreadsheet and completed the following for 2007 version:
http://www.mdmproofing.com/iym/macros.php#excel2007

I still cannot use some of the functions that I had been using before such as Sort and doubling clicking a cell which is supposed to open a calendar and auto function add monetary figures.
Could it be the trial version? Any help is appreciated.

Thanks,
Transformer Man
 

A:Solved: Excel Macro Vista not working

Hi Transformer Man,

I was just wondering, have you solved the problem for Macros working on your Vista Machine?

As I currently working with a collegue to solve this problem, and would like to confer with someone else who has had a similar problem.

Regards,
Tom Griffiths.
 

Read other 3 answers
RELEVANCY SCORE 72.4

Hi.

I have built a macro that imports Data from another worksheet, and to add to the user experience, I want to add a progress bar.

I have added the code, and userform to make this happen, however, it wont update (i.e. show me the progress).

I have tried turning screenupdating on, setting the userforms properties ShowModal=True, and ShowModal=False.

When the ShowModal is set to true, the user form actually displays, but gets itself into a neverending loop and wont quit.
When the ShowModal is set to false, all I get is an outline of the form, but the middle of the box is white and works as normal.

The code is invoked from several different subs as I have different subs that import a different part of the worksheet.

I am using the following code:

Progress.Show
Progress.lblBreakoutNo.Caption = "Currently Importing"
PctDone = BreakoutNo / NoofClaims 'update Progress bar
With Progress
.FrameProgress.Caption = Format(PctDone, "0%")
.LabelProgress.Width = PctDone * (.FrameProgress.Width - 10)
End With
Any help would be appreciated.

Thanks

Lightingman2003
 

A:Solved: Excel Macro - Progress Bar - Not Working

I have now managed to solve it. It was user error, forgot to add "DoEvents" after the ProgressBar.

Hope this helps somebody else out though.

Will now mark as "Solved".

Thanks

Lightingman2003
 

Read other 1 answers
RELEVANCY SCORE 71.6

Hi there

John Walkenbach, the Excel guru who wrote many Excel VBA books, gave me this hyperlink macro that finds Excel workbooks. The macro is complex but I'm sure that someone out there can figure it out. This macro works great in Excel 2000 but does not work in Excel 2013. When I run the macro in Excel 2013 I get a message that says "File not found" and I can't figure out why. I believe that the line "FileType = msoFileTypeExcelWorkbooks" could be causing the problem but I'm not sure.

Thanks

Mario

Below is the code.

Sub Hyperlink1()

Dim i As Long
Dim Folder As String
Dim FileToFind As String

Set objShell = CreateObject("Shell.Application")
On Error Resume Next

Folder = "C:\"
FileToFind = ActiveCell
Select Case ActiveCell
Case ActiveCell
Case Else
MsgBox "The file was not found"
End Select

With Application.FileSearch
.NewSearch
.LookIn = Folder
.SearchSubFolders = True
.FileType = msoFileTypeExcelWorkbooks
If .Execute > 1 Then
If .FoundFiles.Count = 0 Then
MsgBox "File not found.", vbCritical

Else
For i = 1 To .FoundFiles.Count
If Right(.FoundFiles(i), Len(FileToFind)) = FileToFind Then

Workbooks.Open .FoundFiles(i)
End If
Next i
End If
End If
End With
Set objShell = Nothing
End Sub
 

A:Solved: Hyperlink Macro not working in Excel 1013

Apparently MS has degrade the FileSearch over time.

This looks like something to try Microsoft.com Thread
 

Read other 14 answers
RELEVANCY SCORE 62.8

Hello again,

I have a word doc that has a form in it. The form may have any number of fields (boxes) to type text answers into, or be a drop down list to select an answer from. I need an excel macro that extracts all data from the form, but with every five fields starting a new row in excel. So all data from the form will appear within 5 columns in an excel spreadsheet.

field 1 field 2 field 3 field 4 field 5
field 6 field 7 field 8 field 9 field 10
etc

Is that possible? I have had no luck to do it myself.

Sample form attached, thank you
____________________________
 

A:Solved: Excel macro to extract Word form data into Excel

Read other 7 answers
RELEVANCY SCORE 62.8

I’m using Windows 7 and I used a macro which worked totally fine under Excel 2003. Now, under Excel 2007 it does not run anymore but displays the following error message:

"Run-time error '445':
Object doesn't support this action.

The function of the macro is to open up all other Excel spreadsheets located in the same folder and copy data out of them into the spreadsheet in which the macro is stored.

When I click on "Debug" the Visual Basic Editor highlights the row which says: Set FilSrch = Application.FileSearch

Can you help me with this? I've searched for a solution for a long time but could not find anything which worked.

The following is the start of the macro code:

Sub UpdateTable()
Dim X As Range, I As Integer, J As Integer, RecNo As Integer, CopyVal As Variant
Dim SourceBk As Worksheet, DestBk As Worksheet, StartRow As Integer, IndRange As Range
Dim ServRange As Range, SrcOpen As Boolean, SourceName As String
Dim FilSrch As Object, MyFilArray() As String

'MsgBox "This function not available in this version.", vbInformation, "Function Not Available"
'Exit Sub
'Sheets("LookUp").[a12] = Now
With Application
.ScreenUpdating = False
.StatusBar = "Counting source files ..."
End With
Set FilSrch = Application.FileSearch
With FilSrch
.NewSearch
.LookIn = ActiveWorkbook.Path
.FileType = msoFileTypeExcelWorkbooks
If .Execute > 0 Then
ReDim MyFilArray(.FoundFiles.Count)
For I = 1 ... Read more

A:Solved: Excel 2003 Macro Doesn't Run in Excel 2007 - Help Requested

Read other 7 answers
RELEVANCY SCORE 62.8

I am trying to create a code that will autofilter data on an Excel worksheet. The code works fine to filter the data, but it does not hide the AutoFilter Arrows.

Below is the code I am using. What (if anything) is wrong with the code below?
Code:

ActiveSheet.Range("A1:D500").AutoFilter Field:=2, Criteria1:="<>", Visibledropdown:=False
 

A:Solved: Excel 2007 Macro -> Hide Excel AutoFilter Arrows

I noticed that the code above (in Post #1) that the AutoFilter Arrows were beinf truned off on Field 2, but none of the others.

I have played around, and done some research to come up with the code below:
Code:

Dim c As Range

For Each c In ActiveSheet.Range("A1:D1")
ActiveSheet.Range("A1:D500").AutoFilter Field:=2, Criteria1:="<>"
c.AutoFilter Field:=c.Column, Visibledropdown:=False
Next

Does anyone have a better solution?
 

Read other 1 answers
RELEVANCY SCORE 62

Hi guys,

I have been on the lookout for a macro that will compare 2 worksheets within MS Excel and output the differences on a third sheet.

I am being directed to the URL below but cannot for the life of me work out how to implement this:

http://exceltip.com/st/Compare_two_worksheets_using_VBA_in_Microsoft_Excel/477.html

There are two snippets of code which I will include here for convenience but not sure whether these should be pasted into Workbook Code (i.e. right-clicking Excel icon left of "File" - top-left), Worksheet Code (i.e. right-clicking worksheet tab) or inserting a new Module - or some other method. Is there anyone here that could guide me to how to implement this code?

Quote from webpage - "With the macro below it is possible to compare the content of two worksheets.
The result is displayed in a new workbook listing all cell differences. "

Code:
Sub CompareWorksheets(ws1 As Worksheet, ws2 As Worksheet)
Dim r As Long, c As Integer
Dim lr1 As Long, lr2 As Long, lc1 As Integer, lc2 As Integer
Dim maxR As Long, maxC As Integer, cf1 As String, cf2 As String
Dim rptWB As Workbook, DiffCount As Long
Application.ScreenUpdating = False
Application.StatusBar = "Creating the report..."
Set rptWB = Workbooks.Add
Application.DisplayAlerts = False
While Worksheets.Count > 1
Worksheets(2).Delete
Wend
Application.DisplayAlerts = True
With ws1.UsedRange
lr1 = .Rows.Count
lc1 = .Column... Read more

A:Solved: How to use this Macro in MS Excel (compare excel worksheets)

Apologies... I have sorted this... For those that might be looking at this for help here's what I missed:

These are both Sub's that should be created as a Module - both in the same module. I am just comparing worksheets within the Active Worksheet so just removed:

' compare two different worksheets in two different workbooks
CompareWorksheets ActiveWorkbook.Worksheets("Sheet1"), _
Workbooks("WorkBookName.xls").Worksheets("Sheet2")
Click to expand...

I was then able to run this from Macro's. All sorted.

Apologies again
 

Read other 1 answers
RELEVANCY SCORE 62

My clients have a macro that automatically generates an email .. however after our recent upgrade to Office 2003 the macro is producing an error on the send mail portion.

Here is the code:

Sub Send_Email()

Dim OutApp As Outlook.Application
Dim OutMail As Outlook.MailItem
Dim cell As Range
Dim Email1 As String
Dim Email2 As String
Dim Email3 As String
Dim Email4 As String
Dim Email5 As String
Email1 = Range("emailto1")
Email2 = Range("emailto2")
Email3 = Range("emailto3")
Email4 = Range("emailto4")
Email5 = Range("emailto5")
Set OutApp = CreateObject("Outlook.Application")
On Error GoTo cleanup
Set OutMail = OutApp.CreateItem(olMailItem)
With OutMail
.To = Email1 & ";" & Email2 & ";" & Email3 & ";" & Email4 & ";" & Email5
.Subject = "Deal List Update"
.Body = "A transaction requiring special approvals has been entered in the deal list." & _
vbCrLf & vbCrLf & "Trade Date: " & Range("trade_date") & vbCrLf & "Counterparty: " _
& Range("counterparty") & vbCrLf & "Deal Description: " & Range("description")
.Send
End With
Msg = Application.WorksheetFunction.Substitute(Msg, vbCrLf, "%0D%0A")
Set OutMail = Nothing
cleanup:
Set OutApp = Nothing
End Sub

I am hoping it is a simple thing ... really appreciate any assistance.
&n... Read more

A:Excel macro not working after upgrade to 2003

Hi there, welcome to the board!

A couple reasons which may be of issue to you:

1) You will probably need another reference to Outlook 11.0 Object Model (version 2000 was 9.0), or use Late Binding (you are using Early)
2) Your specified ranges are not referenced via worksheet or workbook

So, update the workbook and worksheet in this code and see if it works for you...
Code:
Sub Send_Email()

Dim OutApp As Object ' Outlook.Application
Dim OutMail As Object ' Outlook.MailItem
Dim wb As Workbook, ws As Worksheet
Dim Email1$, Email2$, Email3$, Email4$, Email5$
'// Alter these lines to suit...
Set wb = ThisWorkbook
Set ws = wb.Sheets("Sheet1")
'//
Email1 = Range("emailto1")
Email2 = Range("emailto2")
Email3 = Range("emailto3")
Email4 = Range("emailto4")
Email5 = Range("emailto5")
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0) '(olMailItem)
With OutMail
.To = Email1 & ";" & Email2 & ";" & Email3 & ";" & Email4 & ";" & Email5
.Subject = "Deal List Update"
.Body = "A transaction requiring special approvals has been entered in the deal list." & _
vbCrLf & vbCrLf & "Trade Date: " & ws.Range("trade_date") & vbCrLf & "... Read more

Read other 2 answers
RELEVANCY SCORE 62

Hi, I was coding in VBA and it runs fine there but when I run the same function using a macro (which was working before), it will not do anything. I check that macros are enabled. Does anyone have any ideas as to why the macro button would not work?Hi, I was coding in VBA and it runs fine from there but when I do the same by clicking the macro button (which was working before), it will not do anything. I checked that macros are enabled. Does anyone have any ideas as to why the macro button would not work? Thank you.
- Jeff
 

Read other answers
RELEVANCY SCORE 61.2

Hi,
I have a monthly report I run which outputs single sheet "Invoices" from my workbook that is populated by a months worth of sales. The macro goes through a list of names, pulls the details for that person, populates the pivot table on the invoice,
then saves as both a single excel sheet and a pdf. up until today it has worked fine. the code is, and the error occurs when it reaches the date line. It states "Compile error, can't find project or library":
Sub Save_Sheets_To_New_Books() 'INVOICES
Const strWbPath As String = "D:\Accounts\EA Letters\2020 09 September\"
Dim strDate As String 'todays date
'save the sheets to new books within the active folder and print them
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
strDate = Format(Date, "yyyy.mm.dd")     '(Date, "yyyy.mm.dd")
THIS IS WHERE THE ERROR OCCURS the word DATE is highlighted
ActiveSheet.Copy
Call DeleteNamedRanges
Call PrintAreaAndPasteSpecial
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, FileName:=strWbPath & Name_of_Artist & "_" & strDate & ".pdf" _
        , Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
        :=False, OpenAfterPublish:=False         
ActiveWorkbook.SaveAs FileName:=strWbPath & Name_of_Artist & "_&quo... Read more

Read other answers
RELEVANCY SCORE 60.8

Hey,

I am trying to create a simple Line chart based on values that change daily. However, I'm working in a rather full and complex excel file and the chart would be just a nice touch, so I would love for it to update itself as the values are changed. What I mean is, I don't want to have to enter a date and the value for each date day by day. I was hoping there may be a way for a cell to return its last known value so that I can use the following formula:
Cell B1:
=IF(A1=(Today's date),C1(The value), Last Known Value). This way if it is the correct date then B1 will change to today's value(C1), if not then it ill change to its last known value (so if its specified date was yesterday, yesterday's value will be shown, if not then it can have a default blank value)

Column A will be a series of dates and Column B will end up being that days values.

If this is too much to ask, is there a way to have a daily history of a cell's value be generated somewhere in the same sheet or on another sheet?
 

A:Solved: Return last known value if False in Excel

Figured it out. I Used a nested IF function which returned itself in cases where I didn't want it to change from its previous value.
 

Read other 1 answers
RELEVANCY SCORE 58

Hello
I'm using Excel 2003 (on Win XP). I currently have an excel doc which I use for "form filling". There is a lot of questions, 40 or so on Tab1 and another 40 or so on Tab2. Both use "data, validation, list" for the questions (to sub sub level). Once the form is filled in it is saved as an excel file. Then I fill in the next excel doc, roughly about 50 excel docs are created for each job. I know I should probably use a database but I've never used one before.
What I'm looking for is a macro at the end of the form which I could click and it would collect data from tab1, cell A1, A2, B3, C7 and MOVE it into tab3 to a position A2 – all in a single row (i.e. B2, C2 etc). I can then amend the macro and use it for tab2, so the data is saved in tab4.
Here is the tricky part.
The first reference in cell A1 contains the “Question No” referenced as Q1, Q2, etc as a list box. Sometimes I will need to go back to a previous “question” and amend details but most of the time it’s used for printing purposes at the end of the day. Hence I will need a second macro which when clicked on will ask for “Question Number” and once I input it in, should retrieve the information from Tab2 and insert all the data back into the original cells. I suspect this is impossible as it would then paste the raw data over my “list boxes” making them unusable.
But maybe by having another tab, say “amendments” with a macro button on it, I could select the “questio... Read more

A:Solved: Need a Macro for Excel

Read other 16 answers
RELEVANCY SCORE 58

hi all i've never written a macro before and have been searching online for how to do it but am still pretty confused. i have 4 columns of data, the first of which has a number 1-8 or is blank. if its blank, i want my macro to fill in a number 1-8 based off the next 3 columns. below is how i've thought about how i want my macro to look, but i don't know how to translate this (b, c, d, and e are the columns). any help would be MUCH appreciated. thanks,
If b = "" Then
{If c > 0 Then

(If d > 0 Then

If e > 0 Then b = "1"

If e < 0 Then b = "5")

(If d < 0 Then

If e > 0 Then b = "2"

If e < 0 Then b = "6")}
{If c < 0 Then

(If d > 0 Then

If e > 0 Then b = "3"

If e < 0 Then b = "7")

(If d < 0 Then

If e > 0 Then b = "4"

If e < 0 Then b = "8")}
Else: b = b
 

A:Solved: excel macro help please

Read other 6 answers
RELEVANCY SCORE 58

I need to write a macro to look down all the cells of an entire column of one worksheet and whenever the value of a cell is greater than zero, copy that value and paste to the next empty cell along a different column. For instance, I'm looking down the entire column and D5 is greater than 0, so I paste it to N1,. Next, D8 is greater than 0, so i need to be able to paste this to N2 and so on. Can someone help? Thanks
 

A:Solved: Excel Macro

Hi,

Copy and paste the code to a "Module"
Code:

Sub Check_Zero()
Application.ScreenUpdating = False
Dim cel As Range
Dim lcel As Long
For Each cel In Range("D2:D" & Range("D65536").End(xlUp).Row)
If Cells(cel.Row, 4).Value > "0" Then
lcel = Range("N65536").End(xlUp).Row + 1
Cells(lcel, 14).Value = Cells(cel.Row, 4).Text
End If
Next
End Sub
 

Read other 3 answers
RELEVANCY SCORE 58

Hi,

I have following data with me and this data extends till 1000 rows.

IDSYSTEMASSOCIATED_ABC_PROCESSASSOCIATED_XYZ_PROCESS9ABC Stage IIIABC.101-1XYZ.103-459ABC Stage IIABC.101-2XYZ.103-459ABC Stage IABC.101-3XYZ.103-459XYZ Stage IABC.101-4XYZ.103-459ABC Stage IABC.102-2XYZ.103-469ABC Stage IABC.103-2XYZ.103-479XYZ Stage IABC.103-4XYZ.103-4810ABC Stage IABC.104-1XYZ.105-4-310ABC Stage IIIABC.104-2XYZ.105-4-410ABC Stage IIIABC.104-6XYZ.105-4-510XYZ Stage IIABC.105-1-1XYZ.105-4-610ABC Stage IVABC.106-1XYZ.107-3

I am looking for a macro which will give me results as mentioned below.

IDABC / XYZ METRICABC XYZ STAGES9ABC.101-1
ABC.101-2
ABC.101-3
ABC.101-4
ABC.102-2
ABC.103-2
ABC.103-4
XYZ.103-45
XYZ.103-45
XYZ.103-45
XYZ.103-45
XYZ.103-46
XYZ.103-47
XYZ.103-48
ABC Stage I
ABC Stage II
ABC Stage III
ZYZ Stage I
10ABC Stage I
ABC Stage III
XYZ Stage II
ABC Stage IVABC.104-1
ABC.104-2
ABC.104-6
ABC.105-1-1
ABC.106-1
XYZ.105-4-3
XYZ.105-4-4
XYZ.105-4-5
XYZ.105-4-6
XYZ.107-3



In Column 'ABC / XYZ METRIC' the values from coulmns 'ASSOCIATED_ABC_PROCESS' and 'ASSOCIATED_XYZ_PROCESS' are combined for the respective ID.

Thanks in advance,

Regards,
Akki
 

A:Solved: Excel Macro Help

I don't know how the table are disorganized after submmiting the querry. Submmiting new query with attachment.

Regards,
Akki
 

Read other 1 answers
RELEVANCY SCORE 58

hi... i am really having a problem with creating a macro, could you please help me.

i have to copy content from a auction website, paste it into excel.
this is what the content looks like

Auction - 125458
view bigger image --- name of item
ref number --- start date
end date --- price of item
page views
users tracking
no bids
relists remaining (this is sometimes left out and causes my stress)
copy or relist
edit
close
delete
the above content over 3 columns and over different rows.
i managed a simple macro to put these into column’s, but the problem being that sometimes there is not the same amount of rows with the relists remaining left out sometimes....
so what i need is a macro to put the data above into column’s,
eg... word starting with auction = c1
word starting with view bigger = d1
word starting with item name = e1
---------- then when the next word starting with auction = c2

could you pretty please help me with this
 

A:Solved: excel macro help please

Read other 9 answers
RELEVANCY SCORE 58

I am have a form in excel that when a certain person types his name at the end the following cell will put in date that he signed it. The problem is that when you e-mail the doc. the following day (or whatever day) the date changes to the current date and not the actual date that the doc was signed. (by signed i mean the person typing his name there) Here is the code that is currrently in the doc.
=+IF(F36="Persons Name",H43," ")

I'm not sure if there is some kind of formula for this or do i have to write a macro for it. If i do have to write a macro for it, could someone please help me out with it.
 

A:Solved: Excel Macro

Read other 6 answers
RELEVANCY SCORE 58

Hi Friends,

I'm very much new to Macro in excel. I wanted to Run Macro in the following situation :
I have 2 Excel Book having same header but some data which to be consolidated in a 3rd Book. I recorded macro in 3rd Book the macro function is to " to open Book 1 & Book 2 & consolidate in Book 3" on running the recorded macro.

But after saving the macro and running tha macro again with some new data in Book 1 & Book 2 it gives the following error message "Run-time error '9':
Script out of range

and on debug the following macros are displayed.
---------------------------------------------
Sub Macro2()
'
' Macro2 Macro
'
' Keyboard Shortcut: Ctrl+l
'
Workbooks.Open Filename:= _
"C:\Documents and Settings\Administrator\Desktop\Book2.xlsx"
Workbooks.Open Filename:= _
"C:\Documents and Settings\Administrator\Desktop\Book1.xlsx"
Windows("Report.xlsx").Activate

Selection.Consolidate Sources:=Array( _
"'C:\Documents and Settings\Administrator\Desktop\[Book1.xlsx]Sheet1'!R1:R1048576" _
, _
"'C:\Documents and Settings\Administrator\Desktop\[Book2.xlsx]Sheet1'!R1:R1048576" _
), Function:=xlSum, TopRow:=True, LeftColumn:=True, CreateLinks:=False
End Sub

--------------------

can anyone help. I'll be very much grateful to you.

I'm using excel 2007 but I dont think it has got to do something with macro!

thank you in advance.

Hemen
&nbs... Read more

A:Solved: Help in macro (Excel)

This message usually occurs when the code is trying to call a workbook or worksheet that is not available. Your code opens two workbooks from your desktop (Book2.xlsx, Book1.xlsx) and then is trying to activate a workbook called Report.xlsx. If this workbook is not already open then you will get the error. You need to add some code to open the workbook prior to activating it.

Regards,
Rollin
 

Read other 2 answers
RELEVANCY SCORE 58

I am running Excel 2003. I am creating a macro that open files from a external hard drive. I was hoping to find a way for the macro to find the Drive letter of the external hard drive instead of me having to statically assign the drive letter.

Any help with this would be great.
 

A:Solved: Excel Macro

Read other 9 answers
RELEVANCY SCORE 58

here my situation: I have created 2 worksheets. Both are contact list.
the 1st list is of People who work in the same building as I. The second is
a list of nationwide remote users. My boss emailed me a third sheet
containing names from both list combined. the question I have is can I
write a macro to check the third sheet against the 1st two then seperate
them back out? if you need more info let me know
 

A:Solved: need help with excel macro

Read other 8 answers
RELEVANCY SCORE 58

i have created a spreadsheet that has a list of names down the left, days of the week and dates across the top and a "user' choose in the data fields.The spreadsheets are in one workbook and i have a sheet for each month.

What i need to do is create a form that will enable users to choose a "Date" period ie start and end dates and choose a "name". i then need this form to have a button on it that when clicked would generate an email to the relative person named which would have all of the relevant data in it.

I know how to create the actual form, it's linking everything to it thats the problem, so would really appreciate some help.

Hope this all makes sense !!!
 

A:Solved: Excel macro help req

Read other 15 answers
RELEVANCY SCORE 58

Hello TECHGUYS.
We are using macro fro creating P.O.B. lis ( persons on board) , from the 3500 persons, copying only those who is on board at the moment , then grouping them by company and than sorting in alphabetical order in each group.
Now is the challenge : Is that posiible to modify macro or add another function to it to make it sort personnel of first company in the list by rank / position?
I am almost green in scriptin, but very interesting in it , but now my boss wants to see Himself on top of the list He's last name not starting from A
Thanks in advance...
 

A:Solved: Excel macro help

Read other 12 answers
RELEVANCY SCORE 58

Hello,

I've built an excel spreadsheet and am having trouble with a part of a macro. The relevant section of the macro that I am having trouble with is below:

Application.Run "ATPVBAEN.XLA!Regress", ActiveSheet.Range("$A$1:$A$1094"), _
ActiveSheet.Range("$B$1:$C$1094"), False, True, , "PS stats", False, _
False, False, False, , False

The spreadsheet is basically a template, so as I get new data, I open it up, dump the data in, run the macros and save. The problem is, the data is often different sizes, so one day I may have 1000 rows, and the next 1400. This is not a problem with generating charts, since I can just select that it plots cells 0-2000, and it will ignore cells without data, but if I do this for the above regression, the macro will not fully run and I get an error. It's not really a big deal, as I can open up the macro every time and just edit the range to fit the data I have, but other people often need to use it so I'd like to make it as simple as copy, paste, and run macro1.

Is there a way to insert a macro within the macro so excel automatically searches the column and then runs the regression?

Something like this

Application.Run "ATPVBAEN.XLA!Regress", ActiveSheet.Range("Only rage of cells with data in them"), _
ActiveSheet.Range("$B$1:$C$1094"), False, True, , "PS stats", False, _
False, False, False, , False

I've seen a macro here on how to find cell... Read more

A:Solved: Help with excel macro

Read other 9 answers
RELEVANCY SCORE 58

Hello, I need some help with a macro. I am writing an If...Then...Else statement. The If is based on whether the current cell is blank. This seems simple enough from my experience with Lotus 1-2-3 macros but I can't make it happen. Here is where I am -

If ActiveCell.Value.Isnull = True Then
Blah...Blah...Blah
Else.... Help me! Thanks
 

A:Solved: Excel Macro (VB)

Welcome to the board.

If ActiveCell = "" Then
Do this
Else
Do that
End If

It's the same as IF formula, only in code.

Rgds,
Andy
 

Read other 2 answers
RELEVANCY SCORE 58

Ok not sure if this can be done or not, but hopefully it can.
What I want to create is a macro or something that would read the values in column A, if it finds a duplicate number it would insert a row under it

i.e.

5
5
6
7
8
It would insert a row between the 5 and 6.
Is this possible?
 

A:Solved: Excel Macro - Not sure if it can be done

Read other 16 answers
RELEVANCY SCORE 58

I have an excel report (tab name is "sample report")that lists many rows of data (each row is a new case). In column "H" it lists who is working on each case. Column "I" lists dates. If a date is shown then that means the case is closed. If the cell is empty that means the case is still open.

What I need is a macro that will copy all of the "Open" cases from the "Sample Report" tab and put them into the other tab "Open Case Log" under the specific names in column "H". I have put 5-10 blank rows under each name on the "Open Case Log" tab but I am never sure excalty how many open cases each person will have at any given time. This is where I am having the problem. I have attached the sample excel report.

Any and All help would be greatly appreciated.

Thank you.
 

A:Solved: Need Help with Excel Macro

Read other 16 answers
RELEVANCY SCORE 58

Hello,

I've used this site to create my current excel file; it works pretty good but now it need the evolve and I require some help.
I'm sure there are easier ways to get what I need; I am no expert at macro writting so please bare with me.

THe file current creates graph for me; 'data input' has a button where i input the locations i require a graph for and it simply selects info (from a master file database) and pastes it into 'sheet2' where a graphs exists.

the code is written so that it will go to a pdf print screen for me to input the name and save. this worked well but now there are so many locations that i would like to automate some functions.
what i would like the update to do is the following;

-'results table', column A, "Location" will have several locations listed (only in the white blank spots). I need the macro to generate a graph for each location listed.
-for this to occur, it makes sense that I would also require the pdf to automatically name each file ~ naming it the location that it was looking up (otherwise i will have a bunch of pdf save as files open and i wouldn't know which was which.
any and all input would be greatly appreciated. i also looked up the auto name pdf file; i did not include my attempts to add as it kept getting hung up at the 'save as' file box. i can share my pregress on that if you like.

below is my current code. Please email me so that i can send you the file a... Read more

A:Solved: Excel Macro help

Read other 15 answers
RELEVANCY SCORE 58

I have 50+ excel workbooks and I need to extract 1 tab from each of them and create an individual file for each.

A girl who used to work in my office found a macro or program using excel, that only works on PCs, but I have one available, thatall you have to do is have the selected tab saved so that it opens up to the one you want and paste the entire thing as values and it will do it for you real fast. The alternative is to go in and copy and paste each tab or extract the sheet in each and every one. If anyone can help me find the name of this or where I can find it I would really appreciate it. Oh ya the girls who used to work in my office moved to India, canceled her cell and I do not have her personal email so contacting her is harder than I first thought.
 

A:Solved: Excel Macro?

Read other 14 answers
RELEVANCY SCORE 57.2

In attempting to record a macro I accidentally created a couple macros for which I have no use. How can I delete them?
Tools / Macro brings up a list of macros, but among the action choices offered, the Delete button is grayed out.
 

A:Solved: How delete an Excel macro?

Read other 10 answers
RELEVANCY SCORE 57.2

I have this spreadsheet with a button at the top that runs a macro (I assume) to add a row to a list of contacts. Since the original macro was created, I have added columns of additional information to the spreadsheet. So now, when I use the button to add a contact it only shifts the first 8 columns of information. I want to edit this to have it include the additional columns. However, when I go to Tools-Macros and select the macro in question, I do not get the option to edit it (grayed out). I've checked the worksheet security and the macro security, to no avail. What else could be keeping me from being able to edit this macro?
 

A:Solved: Excel Macro - can't edit

Read other 7 answers
RELEVANCY SCORE 57.2

Hi all,
I'm having a few problems with a macro that sorts data in ascending order in a number of different worksheets. I've created ranges in teh worksheets. There are a number of different ranges but I've attached an example of these (ranges "pm" and "rj"). The macor works on the first range but not on the second when it has to switch worksheets - any ideas!!

Private Sub CommandButton1_Click()
Application.Goto Reference:="pm"
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Selection.Sort Key1:=Range("A4"), Order1:=xlDescending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Application.Goto Reference:="rj"
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Selection.Sort Key1:=Range("A4"), Order1:=xlDescending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
 

A:Solved: Excel - Sort Macro

Read other 7 answers
RELEVANCY SCORE 57.2

I've got a problem with a file I have inherited. It is very complex but works beautifully,... until now. The Macro suddenly has a run time error and crashes out on the first line. When I back the data up by one month, it works fine. There is no change in the script at all. The previous manager of this file mentioned something in passing about how the macro doesn't work right if the file is copied (or something like that) for some unknown reason.

Please Advise.
Thanks,
J
 

A:Solved: Excel Macro works before, but not now???

Read other 12 answers
RELEVANCY SCORE 57.2

I have a userform created in Excel with a combo box. I would like to have the user select an option from a combo box, and then filter/hide data on a worksheet based on the combobox selection.

If any further information is needed please let me know.

Any help with this would be greatly appreciated.
 

A:Solved: Excel 2003 Macro

Read other 16 answers
RELEVANCY SCORE 57.2

This is my first message to this forums so Hi everyone!

to the point. Im trying to make a macro that will help me submit some information to a database. The information will be put in excel table and then exported to a CRM. So I made a userform with all the textboxes and I need to make each textbox user submited text to be pasted to a cell in the table. Do you have any idea how to make this?

So far Ive written this little code but im stuck:
Code:
Private Sub CommandButton1_Click()
Sheets("Hoja1").Select
Range("B1").Select
TextBox1.Text = Range("B1")
End Sub
Thank you!
 

A:Solved: Excel Macro VBA newbie

Try this
Code:

Private Sub CommandButton1_Click()
Sheets("Hoja1").Range("B1").Value = TextBox1.Text
End Sub

Regards,
Rollin
 

Read other 3 answers
RELEVANCY SCORE 57.2

Hi there,
I've cobbled below code together (I'm game but probably not even a pedestrian in VBA, maybe more of a snail) to take some information from an Excel sheet and put it into an e-mail. I also want the active workbook attached to the e-mail and if possible another file with the same name but in pdf (haven't looked at that yet).

The ErrorCatch says it's an object or applications driven error.

I'm not sure about this line: .Attachments.Add ActiveWorkbook.FullName but really have no clue where the problem is.

Any help would be greatly appreciated. Thanks.

Christine

Function GetBoiler(ByVal sFile As String) As String
'Dick Kusleika
Dim fso As Object
Dim ts As Object
Set fso = CreateObject("Scripting.FileSystemObject")
Set ts = fso.GetFile(sFile).OpenAsTextStream(1, -2)
GetBoiler = ts.readall
ts.Close
End Function

Sub Mail_small_Text_Outlook()
'Working in Office 2000-2010

Dim OutApp As Object
Dim OutMail As Object
Dim strbody As String
Dim SigString As String
Dim Signature As String

On Error GoTo Errorcatch
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)

strbody = "Hi Leon" & vbNewLine & vbNewLine & _
"" & vbNewLine & _
"Attached invoice for " & Cells(C14) & ".Can you please approve for payment?" & vbNewLine & _
"Supplier: " & Cells(E43) & vbNewLine & _
"Invoice Number:&quo... Read more

A:Solved: Error 400 in Excel Macro

Read other 14 answers
RELEVANCY SCORE 57.2

Dear All,

I hope this e-mail finds you well. I am trying to set up a macro for a search in excel.

I have a column of locations I am interested in searching in column C. I then have a list of search string (villages in Nigeria) in column A. I would like a macro to choose the first search string (village name) and then search through all the locations in column C. I would like it to then report the text and row number of the cells that contain the village name in any part of the string in a separate worksheet. I then want the macro to choose the next village in column A and do the same thing over again. And so on.

I attach a shortened version of the spreadsheet I am working with.

I have tried searching the forum and doing this myself but I was unsuccessful. Thank you very much for your help in advance.

With best wishes,

Dan
 

A:Solved: Search macro for Excel

Read other 13 answers
RELEVANCY SCORE 57.2

Hi guys

I was searching through the forum and found this post:
http://forums.techguy.org/business-applications/851934-excel-search-macro.html

I was looking to use the same code for the search function.

Sub test()
If ActiveSheet.Name <> "Sheet1" Then Exit Sub
LastRow = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row
Sheets("Sheet2").Range("2:1000").Delete
SearchTerm = Application.InputBox("What are you looking for?")
Application.ScreenUpdating = False
Range("L1") = SearchTerm
Range("L2:L" & LastRow).FormulaR1C1 = _
"=IF(ISERR(SEARCH(R1C12,RC[-11]&RC[-10]&RC[-9]&RC[-8]&RC[-7]&RC[-6]&RC[-5]&RC[-4]&RC[-3]&RC[-2]&RC[-1])),0,1)"
If WorksheetFunction.CountIf(Columns(12), 1) = 0 Then
Columns(12).Delete
Application.ScreenUpdating = True
MsgBox "None found."
Else
For Each Cell In Range("A2:A" & LastRow)
If Cell.Offset(, 11) = 1 Then
Cell.Resize(, 11).Copy Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1)
x = x + 1
End If
Next Cell
Columns(12).Delete
Application.ScreenUpdating = True
If x = 1 Then
MsgBox "1 matching record was copied to Sheet2."
Else
MsgBox x & " matching records were copied to Sheet2."
End If
End If
End Sub

Can anyone please help me to extend this search function over 24 columns of data instead of the 11 as above?
I have very little knowledge of VBA.

Thanks ... Read more

A:Solved: Excel Search Macro

Read other 6 answers
RELEVANCY SCORE 57.2

I have a VBscript that I would like to run with an Excel macro. Can anyone tell me how this can be done?

Is there a way to add the VBscript code to the Excel macro?
 

A:Solved: Excel Macro and VBScript

Sure, it's really the same syntax, paste it and try it.
Do you have the code?
 

Read other 3 answers
RELEVANCY SCORE 57.2

I have a few macros I use for work which pull from other spreadsheets into several cells. So, if I forgot to open these other spreadsheets, the macros not only fails to run properly, but prompts me for the location of the applicable spreadsheets several times.

Is there a way in VBA to have a prompt appear (for those sorts of macros) that reads something like: "Did you remember to open the necessary workbooks?" and runs if I click OK but cancels if I click Cancel or No?

I'm more than willing to do the homework on how to code this myself if need be, but I just want to make sure it can be done and perhaps be given a direction to go in.

Thanks in advance for any help. This has really been a great forum for this sort of thing.
 

A:Solved: Prompt for Excel macro

Read other 9 answers
RELEVANCY SCORE 57.2

Hello to All!

Does anyone have a code/macro that will find a certain text in cells (*total) in a column, then based on the text found, search another column for the totals association with it, format the color & bold that total, and calc the sum in a 3rd cell?
Column 1 would have something like, abcTotals
defTotals
ghiTotals .........etc.
Column 2 ...something like, $63.00 (blue & bold formatting when found) ........ etc.
Column 3 would hold the sum of all the*Totals

Thanks!!!
 

A:Solved: Excel 03 Macro/Code

Read other 8 answers
RELEVANCY SCORE 57.2

I have a userform that contains a textbox for user entry. What is the best way to determine if the user has just hit the spacebar a few times to enter a blank data in the textbox?
 

A:Solved: Excel Macro -> UserForm

I was able to figure it out.

I used the following:
Code:

Len(Trim(Me.Textbox.Value)) = 0
 

Read other 2 answers
RELEVANCY SCORE 57.2

I have a macro in excel which auto fills a specified column with the formula in the first cell.

Assuming that Row 1 contains my headers, the code looks as follows:

Range("M2").Select
Selection.AutoFill Destination:=Range(M2:M1845)

Is there a way instead of specifying the range to ask the macro to just carry on filling the formula until it reaches the end of the data entered, i.e. the last row of data?

I'm sure it was something like Range = xlDown, but every attempt seems to fail.

I am running Excel 2007 by the way.
 

A:Solved: Excel Macro Code

Try using

Selection.AutoFill Destination:=Range("M2:M" & Cells(Rows.Count, "M").End(xlUp).Row)

Rollin
 

Read other 2 answers
RELEVANCY SCORE 57.2

I currently have a macro which I use to assign names in coloumn W. Depending on the total number of rows I equally divide it. This is a sample macro

Sub Macro1()
'
' Macro1 Macro
'
Range("W1").Select
ActiveCell.FormulaR1C1 = "Name1"
Range("W1").Select
Selection.Copy
Range("W2:W561").Select
ActiveSheet.Paste
Range("W562").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "Name2"
Range("W562").Select
Selection.Copy
Range("W563:W653").Select
ActiveSheet.Paste
Range("W654").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "Name3"
Range("W654").Select
Selection.Copy
Range("W655:W675").Select
ActiveSheet.Paste
Range("W676").Select
End Sub

So lets say today I have 22543 rows of data. Now I will divide it by 16 (because I have to assign it to 16 people) 1409. I want to be able to assign 1409 rows to each name total 16 (copy pasting or however) using a macro?

How can this be done without modifying the macro every day, I usually go in and change the numbers under Range.

Thanks
 

A:Solved: Excel Variable macro help

Read other 16 answers
RELEVANCY SCORE 57.2

I have Office 2003 and need the code for a sort macro. I need to sort data in the fields from A3-K64 by data in the K column in descending order. I tried the following code, but it doesn't work:
Range("A3:K64").sortKey1:=Range("K3:K64"),
Order1:=x1Descending,
Header:=x1NoGuess,
OrderCustom:=1,
MatchCase:=False,
Orientation:=x1TopToBottom,
DataOption1:=x1SortNormal

Can you help?
 

A:Solved: excel macro-sort

Read other 6 answers