How JustAnswer Works:
  • Ask an Expert
    Experts are full of valuable knowledge and are ready to help with any question. Credentials confirmed by a Fortune 500 verification firm.
  • Get a Professional Answer
    Via email, text message, or notification as you wait on our site.
    Ask follow up questions if you need to.
  • 100% Satisfaction Guarantee
    Rate the answer you receive.
Ask ATLPROG Your Own Question
ATLPROG, Computer Software Engineer
Category: Programming
Satisfied Customers: 7677
Experience:  MS in IT.Several years of programming experience in Java C++ C C# Python VB Javascript HTML
Type Your Programming Question Here...
ATLPROG is online now
A new question is answered every 9 seconds

Do you help in cleaning and speeding code that I have working

Customer Question

Do you help in cleaning and speeding code that I have working in for Excel macro
Submitted: 1 year ago.
Category: Programming
Expert:  Ingo U replied 1 year ago.
Hi,I would like to review the details of your question.Could you please attach or upload any original files describingthe requirements, work in progress or any other relevant details?For multiple files, it is often easiest to put them all into a compressed(i.e. zip) folder so you only have upload one item.What is your timeframe for this project?Thanks,Ingo PS An easy file sharing site to use is to and upload the file there (no need to sign up). You will then get a page that has the File ID and Download Link. Copy either the File ID (a 6-digit number) or the Download Link.Then come back here and paste it in your reply.
Customer: replied 1 year ago.
Not sure how to upload the file as I have it in Excel. The following is the code:
Sub Prier_7020()
' Prier_7020 Macro
' To Sort by Supplier Sales, smallest to largest.
Application.ScreenUpdating = False
Dim sht As Worksheet
Dim lastrow As Long
Dim LastColumn As Long
Dim StartCell As Range
Set sht = ActiveSheetSet sht = Worksheets("TrueUP")
Set StartCell = Range("A4")'Find Last Row and Column
lastrow = sht.Cells(sht.Rows.Count, StartCell.Column).End(xlUp).Row
LastColumn = sht.Cells(StartCell.Row, sht.Columns.Count).End(xlToLeft).Column'Select Range
sht.Range(StartCell, sht.Cells(lastrow, LastColumn)).Select
' To highlight all Supplier sales that have zero in yellow
lRow = Range("O" & Rows.Count).End(xlUp).Row
Set MR = Range("O4:O" & lRow)
For Each cell In MR
If cell.Value = "0" Then cell.Interior.ColorIndex = 6
'SortByColor Macro
'Range(Selection, Selection.End(xlToRight)).Select ' Commented
'Range(Selection, Selection.End(xlDown)).Select ' Commented
ActiveWorkbook.Worksheets("TrueUP").Sort.SortFields.Add(Range("O5:O156"), _
xlSortOnCellColor, xlDescending, , xlSortNormal).SortOnValue.Color = RGB(255, _
255, 0)
With ActiveWorkbook.Worksheets("TrueUP").Sort
.SetRange Range("A4:AD156")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
End With
' Find first yellow cell and insert 50 rows
With Application.FindFormat.Interior
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Selection.Find(What:="", After:=ActiveCell.Offset(RowOffset:=-1), LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=True).Activate
Application.DisplayAlerts = FalseWindows("03_Prier_Equity Monthly 2016.xls").Activate ' Change File Name each month
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.RowHeight = 16
Range("C:C, F:G, K:N, P:R").EntireColumn.Hidden = True
Selection.TextToColumns Destination:=Range("S4"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=False, _
Semicolon:=False, Comma:=True, Space:=True, Other:=False, FieldInfo:= _
Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1)), TrailingMinusNumbers:=True
Application.Left = 1451.125
Application.Top = 14.5
Windows("PERSONAL.XLSB").Activate ' To Copy VLookup headers
Windows("03_Prier_Equity Monthly 2016.xls").Activate ' Change File Name each month
Application.DisplayAlerts = True
'Find Last Row and Column
Ragne = "A4"
lastrow = sht.Cells(sht.Rows.Count, StartCell.Column).End(xlUp).Row
LastColumn = sht.Cells(StartCell.Row, sht.Columns.Count).End(xlToLeft).Column'Select Range
sht.Range(StartCell, sht.Cells(lastrow, LastColumn)).Select
'Move to cell T5 and enter Fromuals
Range("T5").Select ' change Column
Application.Left = 15.25
Application.Top = 23.125
Application.CutCopyMode = False
ActiveCell.Formula = "=IFNA(VLOOKUP(S5,'March 2016'!$A$7:$H$4185,6,FALSE),0)" ' Change sheet name each month - VL Fromula
ActiveCell.Formula = "=+O5-T5" ' Difference
ActiveCell.Formula = "=PROPER(IFNA(VLOOKUP(S5,'March 2016'!$A$7:$H$4185,3,FALSE),0))" 'City VL
ActiveCell.Formula = "=EXACT(E5,V5)" 'Exact
ActiveCell.Formula = "=PROPER(IFNA(VLOOKUP(S5,'March 2016'!$A$7:$H$4185,2,FALSE),0))" ' Change sheet name each month - Name VL
'ActiveCell.Formula = "=IFNA(MATCH(" * "&LEFT(C5,10)&" * ",X5:X130,0),0)"
'Range("T5:Y5").Select ' Change copy range
Range("T5:X5").AutoFill Destination:=Range("T5:X" & Cells(Rows.Count, "S").End(xlUp).Row)
'Sort Sales by Member IDRange("A4").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
ActiveWorkbook.Worksheets("TrueUP").Sort.SortFields.Add Key:=Range("J5:J62") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataO
Customer: replied 1 year ago.
So can you help with making the variable names and cell ranges more variable (not fixed/static for example = Worksheets("TrueUP"), Sort.SortFields.Add(Range("O5:O156"), .SetRange Range("A4:AD156") etc.
Expert:  Ingo U replied 1 year ago.
It would be best if you could upload the actual Excel file - you can just put in a compressed folder, then attach it here... or go directly to wikisend, where you can upload any file type.Also it looks like the code is referencing other workbooks - "PERSONAL.XLSB" and "03_Prier_Equity Monthly 2016.xls" for example, maybe I missed others. I would need those too in order to make and test any changes to your macro code.I will also need a better understanding of what you mean by "more variable" - describe how you would like to be able to use the macro when it's all done - for example, if not using a fixed Range("O5:O156"), where would that piece of information come from?
Customer: replied 1 year ago.
I have searched on the internet and they all state that it is good coding to use variable range instead on the fixed range that I have in my code. My code is very small and I figured just by looking at the code maybe you can clean it up. I will try and upload those files.
Customer: replied 1 year ago.
Hopefully you can use the Excel macro file that is stored in C:\Users\Name\AppData\Roaming\Microsoft\Excel\XLSTART\PERSONAL.XLSB
Expert:  Ingo U replied 1 year ago.
I'm sorry,After reviewing the question, I regret that I won't be able to assist you at this time.I am opting out and opening the question up to other professionals here who may be able to help.Regards,Ingo U
Customer: replied 1 year ago.
OK. Thanks
Expert:  James Feazell replied 1 year ago.
Hi. My name is***** would be happy to review your macro with you.
Customer: replied 1 year ago.
Are you able to read the messages that I had with the previous programmer? I am attaching the zip file. Let me know if this is something you can help. It is "simple" hopefully. I am new to this and hence some issues.
Expert:  James Feazell replied 1 year ago.
Yes, I see the previous messages and I believe I can assist you. Why was the previous expert not able to assist? I see where they opted out, but they never really said why.
Customer: replied 1 year ago.
This is all he sent me - After reviewing the question, I regret that I won't be able to assist you at this time
Expert:  James Feazell replied 1 year ago.
I see. In order to expedite this, I would like to speak you directly. Would you be alright with that? I will not add any extra charge to the service.
Customer: replied 1 year ago.
Sure. Or I can call you. My # *****(###) ###-####