MAX will take a range and tell me the largest number. But what if I wanted to iterate over that range and find the largest two numbers in a row?
For example, if I have the range [0, 2, 5, 6, 9, 3, 8], MAX is 9, but MAX2 is 15 (6+9). MAX3 is 20 (5+6+9).
How would I write MAX2, MAX3, or MAXN in Excel?
here is my code:
import random
from random import shuffle
fileName = 'test'
dataFile = open("DD" + fileName+'.csv', 'w')
dataFile.write('trial,numbers') #data headers for .csv file
a = [1,2]
for i in range(4):
shuffle(a)
if a == [1,2]:
num = 'top'
else:
num = 'bottom'
print a
print num
info = []
i = str(i)
info.append(',')
info.append(i)
info.append(',')
info.append(num)
dataFile.writelines(info)
This code randomizes an order than labels it top or bottom 4 times, and exports the a excel file. The problem is that when it exports it puts it in a row. What I would like to do is have it exported into a column so that they are under the headlines 'trial' and 'numbers'. Any help would be greatly appreciated. :)
I want to transpose data given in rows to columns.
The Excel Paste Special -> Transpose on the full dataset goes by rows instead of by column.
| 1 | 2 | 3 | 4 | 5 |
|---|---|---|---|----|
| 6 | 7 | 8 | 9 | 10 |
| 1 |
| 6 |
| 2 |
| 7 |
| 3 |
| 8 |
| 4 |
| 9 |
| 5 |
| 10 |
I'd like to automate this process, however, I'm not too familiar with VBA. Thanks for any and all help.
I found this worked well for an Excel Range, where the result would be array(n) notation instead of array(1, n) enter code here Result = Application.Transpose(Application.Transpose(Worksheets(kSheet).Range("Y20:AC20")))
However I have a result from .getrows which is array (n,0) notation. Can this be converted to arry(n) notation simialr to above ?
I'am a beginner with macros in excel. I'am trying to make a VBA-code that change the pattern fill when 2 other cells are equal to each. I have the follow code:
Private Sub Workbook_Open()
' Macro2 Macro
' If (H5)=(J20) Then
Range("H7").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent2
.TintAndShade = 0.799981688894314
.PatternTintAndShade = 0
End With
If (H5) <> (J20) Then
Range("H7").Select
With Selection.Interior
.Pattern = xlNone
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Range("M20").Select
ActiveWorkbook.Save
End If
End Sub
The first part of the code is working but when you change one of the 2 cells then the pattern is not changed back in no Filling. Can someone tell me what is wrong in the code?
Now is the macro only running when you open the workbook. Is it possible that the macro directly is running when you changed a cell?
Now I write the macro for Cell H7 that compares H5 with J20. I want that the macro H5 compares with J20:J29, is this possible one a "easy" way?
Last question: Is it also possible to use the macro for more cells in the same sheet, for example E5-E7,F5-F7,G5-G7,.....NK5-NK7 with the same kollom to compare (J20:J29)?
All suggestions are welkome.
Yes, its a kind of conditional formatting. But I can't find the right formula/code for conditional formatting a cell by compare 2 other cells.
E.g. "E7 is gray when the date in E5 is equal on the date in J20 or J21 or J22 or ... or J29 otherwise E7 is not filled" and
"F7 is gray when the date in F5 is equal on the date in J20 or J21 or J22 or ... or J29 otherwise f7 is not filled" and that so on up to "NK7 is gray when the date in NK5 is equal on the date in J20 or J21 or J22 or ... or J29 otherwise NK7 is not filled".
Will you please help me how can i access the name from one sheet to another sheet . the main thing is when i call the data from (sheet1!A1) after that 14 row should be blank in sheet 2. and on the row 15 of sheet 2 should store ( sheet1! A2) data. I have created such type of format on Sheet2
NAME :- Rozer 1 Roll-no 801
14 time blank line after that
NAME :- Sammul Roll-no 802
}}}}
All information are accessing from sheet1 . i want if i enter the data in sheet one, then sheet 2 automatically create such type of forms.I hope you will suggest me the best formula.The main problem arising in ( NAME & Roll-No ) column. Thank you !
I am using an excel spreadsheet formula to add two cells together and I want to have it reference one row from left to right like it normally does, but I need it to reference the same row in another page in reverse, and then be able to drag the formula through the entire worksheet. I cannot seem to find anything like this when I look online. TLDR I need to know how to tell excel reference the cell to the left if I drag it to the right.
so I need to add A0 in page one and A9 in page two then when I copy the formula to the left I need it to grab A1 and A8, is there a way to get this?
Background Information
I have an inputbox to get a date from my user to process the sub procedure.
Problems
cells(1, columns.count) invalid qualifier could you please explain why?Sub functionLoop()
Dim Nextoffday As Date
Dim i As Integer
Dim selectionRow As String
Nextoffday = Application.InputBox(prompt:="Please select the second off day.", Title:="Pick second off day", Type:=8)
For i = 1 To 30
If AFPDAY(Nextoffday + i) <> "" Then
ThisWorkbook.Worksheets(1).Cells(1, Columns.Count).End(xlToLeft).Offset(0, 1) = AFPDAY(Nextoffday + i)
End If
Next i
End Sub
i need to import a lot of excel tests to hpalm in the same time how can i create this VBA excel macro
I have two sheets
SHEET A contains more than 1500 entries like this
A B C
Year Month Births
1880 1 530
1880 2 456
1880 3 234
1890 1 163
1890 2 123
1890 3 125
Sheet 2 is similar but column C has no entries and there are only a few years and months in random years and months:
A B C
Year Month Births
1880 1
1890 2
1890 3
I am trying to find a formula to return the value from Sheet 1 | column C to Sheet 2 | column C if column A and B in Sheet 1 are the same as in column A and B in sheet 2.
I am looking for a formula, no VBA since I can't always use it.
And been unable to adapt anything I find here at stackoverflow.
thx
It would be great if anyone could help me, i need to find various datas in a sheet and select those data and print the selected data to printout and all datas to be printed in one page. i tried with this code but something wrong:-
Sub Selection()
Dim varRow As String
For i = 1 To Range("A" & Rows.Count).End(xlUp).Row
If Range("A" & i).Value = "M655" Or Range("A" & i).Value = "Equity Fund" Then
If Trim(varRow) <> "" Then
varRow = varRow & "," & i & ":" & i
Else
varRow = varRow & i & ":" & i
End If
End If
Next i
Range(varRow).Select
Selection.PrintOut
With ActiveSheet.PageSetup
.PrintTitleRows = "$3:$3"
.PrintTitleColumns = "$B:$B"
.Orientation = xlLandscape
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = 1
End with
End Sub
Kindly help me out.
Thanks & Regards
I am new to this group and VBA.
I have a worksheet 'T2', with a variable number of rows. Starting with row 3, I want to look up the item number found in column 'E' and search worksheet 'Support'. The item number can be found somewhere in column 'A' in 'Support'. I need to bring the value in column 'B' in Support (that relates to that item number) and store it in column 'A' in worksheet 'T2'. I then want to repeat this process for every row in 'T2' up to and including the last row.
Can anyone help please as I just can't get this to work at all.
Thanks
This script is supposed to:
1. Scroll down Column B where the Depts are located.
2. Next, select the entire Row of Data from Col A to Col F
3. Create a New Worksheet with the name of the Dept in Col B
4. Paste that Entire Row that was selected in the newly created worksheet
5. And then, move on to the Next Row until the End of the Data on the Original Data Sheet
6. If the Dept value is different from that of the previous row in Col B, then a New Worksheet is created and the routine begins again on the next Worksheet.
I suck and it fails at the IF Then Statement. Does AnyOne have an Idea, so I can move forward from this.
For some reason, the code is broken at the IF Then Statement
Sub Breakout()
Dim FinalRow As Long, I As Long
Dim valuenewsheet As String
Dim Sht As Object
FinalRow = Range("A" & Rows.count).End(xlUp).Row
MsgBox (FinalRow)
ActiveSheet.Range("B1").Select 'selects value in B1
valuenewsheet = (ActiveCell.Value) 'sets value as variable
Sheets.Add.Name = valuenewsheet 'creates new sheet
Worksheets("Sheet1").Select 'reselects original sheet where data is
Set Sht = ThisWorkbook.Sheets("Sheet1") 'sets org data sheet as sht
For I = 1 To FinalRow Step 1 'initiates a loop
Range(Sht.Cells(I, 6), Sht.Cells(I, 1).End(xlToLeft)).Select 'creates a range of data frm colA to colF one a single row
Selection.Copy 'copies this data
Sheets(valuenewsheet).Activate 'activates newly created sheet
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False 'pastes data frm slctd range
ActiveCell.Offset(1, 0).Select 'while on new sheet, select next row
Sht.Activate 'activate org. data sheet
If Sht.Cells(I, 2) <> Sht.Cells(I - 1, 2) Then
Sheets.Add.Name = Sht.Cells(I, 2).Value
Worksheets(Sht).Select
Else
End If
Next I
End Sub
I have a problem with creating a excel. While I created macros before using the record function, it does not work for this problem. Any help would be greatly appreciated!
I have to import a bunch of delimited text files into excel and while the wizard is of great help I'd like to automate the whole process.
What I did was to record the process of importing one file with the wizard. When I rerun the recorded excel I get the following error, though: "Run time error '5': Invalid procedure or call argument"
When I debug it shows an error in the .CommandType = 0 line.
I have two questions, how do I get rid of the error message and more importantly, how can I change the code so that a file input window will pop up every time I run the macro? (I tried to embed the Application.GetOpenFilename() command, to no success, unfortunately)
Thank you already for any help!
Cheers, dahlai
This is my code:
Sub ImportDataPeriod1()
'
' ImportDataPeriod1 Macro
'
'
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;F:\pETMEOH100 15xx DHAP.txt", Destination:=Range("$A$3"))
.CommandType = 0
.Name = "pETMEOH100 15xx DHAP"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 850
.TextFileStartRow = 37
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 1, 9, 1, 9, _
9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9 _
, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
End Sub
How do I take the Macro from my xslm file and apply it to my xlsx file?
import cx_Oracle
import xlsxwriter
import win32com.client
SQL = "Select * FROM TABLE"
cursor1 = con.cursor()
cursor1.execute(SQL)
workbook = xlsxwriter.Workbook('OUTPUT.xlsx')
worksheet = workbook.add_worksheet('Summary - Attendee')
worksheet.set_tab_color('red')
for i, row in enumerate(cursor1):
for j, col in enumerate(row):
worksheet.write(i+1,j,col)
excel = win32com.client.Dispatch('Excel.Application')
excel.Visible = 1
excel.Workbooks.Open(Filename="Path\Macro.xlsm")
excel.Application.Run("Format")
excel.Workbooks(1).Close(SaveChanges=1)
excel.Application.Quit()
excel = 0
workbook.close()
Macro.xlsm VBA code:
Sub Format()
Sheet1.Select
Cells.Select
Cells.EntireColumn.Autofit
End Sub
Both of these work but as separate processes and files. I want to call the Macro from xlsm and apply it into my xlsx file to format my xlsx file. Any ideas?
Can some one help me with the IF Formula. I need an IF formula that will return value of "Open" or "Closed" by evaluating the condition where if I have a cell as below. I need the B3 to be automatically provide status whether open or close after checking Cell A1 if got data and if CellE1 got data then it should return Closed in cell B1 else if the Cell A2 got data but the cell E2 got no data it should return value "Open" in B2.
I tried something like this but didn't work:
=IF(AND(A2,E2=">0"),"Open",IF(AND(A2,E2"<0"),"Closed"))
Actual sheet:- No Case Status Date Created Expected Closure Date Closed 1 3095 Open 03-Dec-14 02-Jan-15 03-Dec-14 2 3096 Open 02-Dec-14 01-Jan-15
3 3097 Open 03-Dec-14 02-Jan-15 04-Dec-14
Hey guys I need help please. I wrote code that conditionally formats cells that contain a specific number then it sorts the colored cells to the bottom and finally I wan't to sum the cells that only contain the interior color. The problem is when I execute the function sumbycolor excel doesn't recognize the cells colors and sums the entire column (color or no color). How do I fix This? Thank you!
Sub MySheet
'Conditional formatting
Range("A1").CurrentRegion.Select
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=$F1=99999"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
'Sort by color column C
Dim sht As Worksheet
Dim rngSort As Range
Dim rngTable As Range
Set sht = ActiveSheet
RowCount = sht.Range("A1").End(xlDown).Row
Set rngSort = sht.Range("A1:A" & RowCount)
Set rngTable = Range(Range("A1"), ActiveCell.SpecialCells(xlLastCell))
sht.Sort.SortFields.Clear
sht.Sort.SortFields.Add(rngSort, _
xlSortOnCellColor, xlDescending, , _
xlSortNormal).SortOnValue.Color = RGB(255, 255, 0)
With sht.Sort
.SetRange rngTable
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
'Sum by color
lr = Cells(Rows.Count, "O").End(xlUp).Row - 1
sumRange = Range("O2:O" & lr).Address
lr = Cells(Rows.Count, "P").End(xlUp).Row - 1
sumRange2 = Range("P2:P" & lr).Address
lr = Range("B" & Rows.Count).End(xlUp).Row
CellColor = Range("B" & lr).Address
Range("C1").Select
ActiveCell.End(xlDown).Offset(9, 0).Formula = "=(ColorFunction(" & CellColor & "," & sumRange & ",TRUE)+ColorFunction(" & CellColor & "," & sumRange2 & ",TRUE))"
End Sub
I understand that fitlercontext propagates from one table to another table along relationships and that it only propagates from the one side to the many side.
I can't see why DAX would not propogate it's fitlers from the many to the one side. I've been reading the book from http://ift.tt/1jNrDrH and other DAX books (by the same well known authors) and I just don't get why it would need to work like this.
See my example here: My OneNote page
This is very counter intuitive for me, and it makes me very uncomfortable. If I had written a SQL statement selecting data from tables that were related with a variety of relationships, any "filters" (ie WHERE clauses) on any of the tables reduces down the dataset that was created through including all the joined tables in the tables used. (ie the filters propagate)
I'm quite new to DAX so may be I just need to keep reading. However, in my example pivot table above, as a user of the data model, I would have really have liked to have been able to get data from the DimTime table.
If you have any useful thoughts I would very much appreciate it if you could share them!
Thanks in advance, Harvey
MapQuest has changed their XML file and I'm no longer able to retrieve the distance and the time.
My VBA script is hung up on the following:
Set req = New MSXML2.XMLHTTP
req.Open "GET", url, False
req.send
resp = req.responseText
Set xdoc = req.responseXML
refMiles = CSng(xdoc.SelectSingleNode("response / route / distance").Text)
refHours = CLng(xdoc.SelectSingleNode("response / route / time").Text) / 60 / 60
Does anyone have an idea how I can correct this?
I'am trying to make a portion of text in a textbox bold and the rest of the text stays normal.The textbox is in excel of course I've used this code to create it.
Microsoft.Office.Interop.Excel.Shape t1 = ws.Shapes.AddTextbox(Microsoft.Office.Core.MsoTextOrientation.msoTextOrientationHorizontal, 20, 15, 200, 77);
I tried doing that by adding html tags but it seams useless because they just behave as normal text. I appreciate some help thanks;
As you can see here, I need a formula that uses the supplier ID in the bottom table and checks for matching supplier IDs in the top table. Then sum up the (Quantity per re-order)*(unit price) IF the "amount in stock" <= "Re-Order level"
I'm super stressed out about this and would really appreciate some help in this area.
Thanks a lot
I want to automatically backup twitter search results, 24/7, to a server or cloud account. Specifically, I want to save images results,Tweets with images embedded (such as KEYWORD + pic.twitter.com)
How can I do so?
I would like to count the number of occasions a report has been logged (column c) in the last 50 days, excluding cases where reports have been marked as "Test" in column E.
I have a working formula for the first half, but I can't seem to get a figure out how to exclude instances where column E is marked as a "test".
=COUNT(FILTER('Report Log'!C:C,DATEDIF('Report Log'!C:C,NOW(),"D")<5000))
Any advice much appreciated!
When I ctrl-f in a spreadsheet, I can find the text, "child Support," but when I'm using it as a pattern it fails. Other obvious words work, like "Date," for instance.
This spreadsheet was converted from a PDF. Could there be some hidden null characters that are causing this column to be ignored?
Private Function RegExTest(s As String) As String
Dim re, match
Set re = CreateObject("vbscript.regexp")
re.Pattern = "(child support)"
re.Global = True
For Each match In re.Execute(s)
MsgBox match.value
RegExDate = match.value
Exit For
Next
Set re = Nothing
End Function
(Apologies if this code is rough, I'm a contractor and the box I'm really on is nannied by some bs so I can't copy and paste.)
I am copying data from Excel to a Word document. In some cases depending on how much data is copied some of the tables span over more than one landscape page.
Q Is there a way to determine if the data I am pasting is going to go over the page, so I can resize it so it fits neatly on one page?
seeking EXCEL function to count without resorting SS. I'll be pasting this function in the SS everyday using "dataload" - to look for rows that are started (date entered), not completed (no date), and > than 2 days (date started) The 3 criteria are 1. if cell notblank - A1:A10 2. if another cell isblank, B1:B10 3. if date in A1:A10 is older than 2 days from current So, in the linked example - the result of the function should = 2 only counting the 2 rows shown in green. I added for problem solving only a "Count logic" & "Function comment" for explanation only I've tried COUNTIFS to see of adjacent cell is blank, and with =TODAY-2 days but I'm stumped - any help appreciated SS link - can't post images yet
I have this function working ( It returns the row where the text DK001 sits in the ID range)
Found = Application.Evaluate("=IF(ID=""DK001"",ROW(ID),""x"")")
I would like to feed the searchcriteria (e.g. DK001) as a string, like
Found = Application.Evaluate("=IF(ID=SearchString,ROW(ID),""x"")")
I fail in creating a string that is accepted as a search criteria. I need your help on this! What am I doing wrong?
This Evaluate function is haunting me ....
What if I now wanted to send a value (not a string) to the function?
Found = Application.Evaluate("=IF(ID=1,ROW(ID),""x"")")
The above works!
But if I want this to be a variable like
Found = Application.Evaluate("=IF(ID=MyValue,ROW(ID),""x"")")
What then?
i created pivot table from external data source (ODBC/mysql). Everything runs fine. Thank I saved the file. But When I try to refresh data source after new open file, excel give me a shutdown.
Any help?
I want to implement a custom sort in excel but my custom list is very long. and excel says "The maximum length for a custom list has been exceeded. Only the first 255 characters will be saved." Now what to do? Any suggestion appreciated.
How do I use greater than in this code?
ActiveSheet.Range("$A$1:$BG$5158").AutoFilter Field:=13, Criteria1:= _
">"Range("BJ1").Value, Operator:=xlAnd
Here is the complete code
Sheets("New Hire").Select
Range("CI1").Select
Selection.Copy
ChDir "D:\Sales Report"
Workbooks.Open Filename:="D:\Sales Report\Global_New_Hire_Report.xlsb"
Windows("Global_New_Hire_Report.xlsb").Activate
Range("BJ1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("A1").Select
Selection.AutoFilter
Windows("Global_New_Hire_Report.xlsb").Activate
ActiveSheet.Range("$A$1:$BG$5158").AutoFilter Field:=13, Criteria1:= _
">"Range("BJ1").Value, Operator:=xlAnd
I am trying to scrape the table contents from a serie of url's. I have been working on the code below, which execute the following steps: 1. Add new worksheet based on value in Range A1:A3 in worksheet "Start" (1, 2, 3, etc) 2. Create url based on the same value (, 2, 3, etc) 3. Activate new worksheet 4. Open URL and scrape table
The following happens: 1. New worksheets are added (1, 2, 3) 2. Worksheet("1") contains the table from 3. Worksheets("2") and following remain empty
Where is the mistake?
Sub TableExample()
Dim IE As Object, doc As Object
Dim strURL As String
Dim ws As Worksheet, wsActive As Worksheet
Dim i As Long, tabno As Long, nextrow As Long
Dim cell As Range
Dim MyNames As Range, MyNewSheet As Range
Dim tbl As Object, rw As Object, cl As Object
Set ws = Sheets("Start")
With ws
Dim rng As Range
Set rng = .Range("A1:A3")
For Each cell In rng
Sheets.Add.Name = cell.Value
Set wsActive = ThisWorkbook.ActiveSheet
strURL = "http://xxx&pagenumber=" & cell.Value
Set IE = CreateObject("InternetExplorer.Application")
With IE
'.Visible = True
.navigate strURL
Do Until .readyState = 4: DoEvents: Loop
Do While .Busy: DoEvents: Loop
Set doc = IE.document
With wsActive
For Each tbl In doc.getElementsByTagName("TABLE")
tabno = tabno + 1
nextrow = nextrow + 1
Set rng = wsActive.Range("B" & nextrow)
rng.Offset(, -1) = "Table " & tabno
For Each rw In tbl.Rows
For Each cl In rw.Cells
rng.Value = cl.outerText
Set rng = rng.Offset(, 1)
i = i + 1
Next cl
nextrow = nextrow + 1
Set rng = rng.Offset(1, -i)
i = 0
Next rw
Next tbl
End With
End With
Next
End With
IE.Quit
End Sub
I use extensive Excel-Word linking, updated manually with a macro. If the Excel file is not open, Word attempts to open it, warns that opening it again is trouble, requires a dialog box be answered, then go to the next link and starts again. Since there are usually 100 links, this is a big problem.
I found a simple macro that calls a function to check if Excel is running . . .
Dim sApp As String sApp = "Excel.Application" If IsAppRunning(sApp) = True Then MsgBox "Excel is Running, continue" & SourceFileName Else MsgBox "Excel is NOT Running, abort" Exit Sub End If
. . . but I want to check to see if the RIGHT Excel file is open. The "right" file will have the same name as the Word file, e.g., MyDocSample.doc will be linked to MyDocSample.xls.
If MyDocSample.xls is open, the macro should continue, if not, a warning should be posted saying "MyDocSample.xls" is not open, and offer an Abort selection.
Thanks in advance, Dave
like I said, I need a cell to display the result of a VLOOKUP and then allow the user to select a different value from a dropdown list, if they choose too. I used data validation list to create a dropdown with all of the possible values a user could want. Based on the input from a separate cell, I want to use VLOOKUP to find the value I want and display it. But if the user doesn't like the resulting value, they need to be able to change it by picking a different value from the dropdown list.
When I create the dropdown full of all possible values, I cannot enter a formula overtop of the list. When I try I get a message that says "The value you entered is not valid. The user has restricted values that can be entered into this cell."
So then I tried putting the formula in the cell before I used data validation to create the dropdown. This worked the first time I entered a value into the criteria cell. I was then able to select a different value from the list. Unfortunately this only worked once. After I selected a different value in the list the formula would never update when the criteria cell for the VLOOKUP was changed.
Can anybody help me accomplish this? Thanks!
I have a workbook with a userform. Then I have 2 procedures to show and hide this form in a code module of this workbook respectively.
In workbook module I have have assigned an onkey event to show this userform.
The Problem:
When I am on excel 2010 or 2013 and press this combination this menu is shown in irrespective of which workbook is active. Whereas in excel 2007 this key combination only works when this workbook is active.
Have tried checking the show hide procedure those are also not visible in another workbooks so even I cannot run then too by selecting from Macro list (Alt + F8).
How to show this userform on any of the active workbook in excel 2007 too?
Below is a link for a sample workbook:
This is in ThisWorkbook
Private Sub Workbook_Open()
Application.OnKey "^{s}", "showForm"
End Sub
The following is in the Module 1 of the workbook:
Sub showForm()
UserForm1.Show
End Sub
Sub hideForm()
UserForm1.Hide
End Sub
Now this Alt + S shortcut to bring userform in view while other workbook is active is working only in excel 2010 and 2013 but not 2007.
Hi I am trying to copy multiple columns from one workbook to another, and below is the code how I copied one and need help in making the code more optimized as I don't want to write same code for all the columns. below is the code.
Sub Copymc()
Dim x As Workbook
Dim y As Workbook
Set x = Workbooks.Open("H:\testing\demo\test2.xlsx")
Set y = Workbooks.Open("H:\testing\demo\test1.xlsx")
Dim LastRow As Long
Dim NextRow As Long
' determine where the data ends on Column B Sheet1
x.Worksheets("Sheet1").Activate
Range("A65536").Select
ActiveCell.End(xlUp).Select
LastRow = ActiveCell.Row
' copy the data from Column B in Sheet 1
Range("A2:A" & LastRow).Copy
' Determine where to add the new data in Column C Sheet 2
y.Worksheets("Sheet1").Activate
Range("A65536").Select
ActiveCell.End(xlUp).Offset(1, 0).Select
NextRow = ActiveCell.Row
' paste the data to Column C Sheet 2
y.Worksheets("Sheet1").Range("A" & NextRow).Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("A1").Select
End Sub
I tried to put all columns in range statement but problem I found was how to paste? How can I do it for multiple columns without repeating the code? Thanks in advance.
Good day everyone, Can Anyone help me with my query? In the VBA Codes below, it executes a program to convert csv files into excel files in a different sheet (SHEET2) BUT in same WORKBOOK.
I am wondering if there is any way I can change that instead of the CSV files going into SHEET2, the excel can prompt me to CREATE A NEW EXCEL WORKBOOK and asks for the name of the Workbook and possibly its new location.
Note: The code below is just a block from the whole block of codes to execute the conversion of CSV file to Excel File. Reason is it is very long and I think that the programs preceeding the one's below is irrelevant to my real question.
So, if anyone needs the whole code I can just send it to your emails :)
Thank you!
Sub ImportFile()
Dim sPath As String
Dim intCoice As Integer
Dim strPath As String
Dim FilePath As Sting
'change the display name of the open file dialog
Application.FileDialog(msoFileDialogOpen).Title = _
"CSV File Opener"
'Remove all other filters
Call Application.FileDialog(msoFileDialogOpen).Filters.Clear
'Add a custom filter
Call Application.FileDialog(msoFileDialogOpen).Filters.Add( _
"CSV Files Only", "*.csv")
'only allow the user to select one file
Application.FileDialog(msoFileDialogOpen).AllowMultiSelect = False
'make the file dialog visible to the user
intChoice = Application.FileDialog(msoFileDialogOpen).Show
If intChoice <> 0 Then
'get the file path selected by the user
strPath = Application.FileDialog( _
msoFileDialogOpen).SelectedItems(1)
Else
MsgBox "Wrong CSV File. Please Choose Again"
End If
'Below we assume that the file, csvtest.csv,
'is in the same folder as the workbook. If
'you want something more flexible, you can
'use Application.GetOpenFilename to get a
'file open dialogue that returns the name
'of the selected file.
'On the page Fast text file import
'I show how to do that - just replace the
'file pattern "txt" with "csv".
sPath = ThisWorkbook.Path & strPath
'Procedure call. Semicolon is defined as separator,
'and data is to be inserted on "Sheet2".
'Of course you could also read the separator
'and sheet name from the worksheet or an input
'box. There are several options.
***copyDataFromCsvFileToSheet sPath, ";", "Sheet2"***
End Sub
Excel 2013. This is my PIVOT table where the item labels are in tabular form. What I am trying to do is sorting as follows :
How can I accomplish this? I tried more sort options. No use. Can this be done using excel VBA?
I am using VB.net to to run some code which gathers the PC's hardware specification and OS version etc. (which I found and modified from the net)
When I run the code, it displays all of the above information onto a console application. I need the information gathered to be inserted into cells on an already existing excel document. For example when the console application displays 'OS VERSION - Windows 7', I need that to be transferred into a cell on an already existing excel sheet. Is this an easy thing to do or is it even possible?
I am not really VB.net literate..
Below is the code for 'module1' and the code for 'WMI'
Module Module1
Dim bit As String
Dim VGA As String
Sub Main()
If My.Computer.Registry.LocalMachine.OpenSubKey("Hardware\Description\System\CentralProcessor\0").GetValue("Identifier").ToString.Contains("x86") Then
bit = "32-bit"
Else
bit = "64-bit"
End If
Console.Title = "System Information's"
Console.WriteLine("your system information's")
Console.WriteLine("")
Console.WriteLine(My.Computer.Info.OSFullName.ToString())
Console.WriteLine(My.Computer.Info.OSPlatform.ToString())
Console.WriteLine(My.Computer.Info.OSVersion.ToString())
Console.WriteLine("Windows bit version: " + bit)
Console.WriteLine("Computer Name: " & My.Computer.Name.ToString())
Console.WriteLine("Current Date/Time: " & Date.Now.ToLongDateString + ", " + Date.Now.ToLongTimeString)
Console.WriteLine("")
Dim objWMI As New WMI()
With objWMI
Console.WriteLine("Computer Manufacturer = " & .Manufacturer)
Console.WriteLine("Computer Model = " & .Model)
Console.WriteLine("OS Version = " & .OSVersion)
Console.WriteLine("System Type = " & .SystemType)
Console.WriteLine("Windows Directory = " & .WindowsDirectory)
End With
Console.WriteLine("")
Console.WriteLine("Number of Processes: " & Environment.ProcessorCount.ToString)
Dim moSearch As New ManagementObjectSearcher("Select * from Win32_Processor")
Dim moReturn As ManagementObjectCollection = moSearch.Get
For Each mo As ManagementObject In moReturn
Console.WriteLine("Processor: " & (mo("name")))
Next
Dim ramsize As Integer
ramsize = My.Computer.Info.TotalPhysicalMemory / 1024 / 1024
Console.WriteLine("Memory: " & ramsize.ToString & "MB RAM")
Console.WriteLine("")
Dim WmiSelect As New ManagementObjectSearcher _
("root\CIMV2", "SELECT * FROM Win32_VideoController")
For Each WmiResults As ManagementObject In WmiSelect.Get()
VGA = WmiResults.GetPropertyValue("Name").ToString
Next
Console.WriteLine("Computer Display Info: " & VGA)
Dim intX As Integer = Windows.Forms.Screen.PrimaryScreen.Bounds.Width
Dim intY As Integer = Windows.Forms.Screen.PrimaryScreen.Bounds.Height
Console.WriteLine("Screen Resolution: " & intX & " X " & intY)
Console.WriteLine("")
Console.WriteLine("Total Physical Memory: " & My.Computer.Info.TotalPhysicalMemory.ToString())
Console.WriteLine("Total Virtual Memory: " & My.Computer.Info.TotalVirtualMemory.ToString())
Console.WriteLine("Available Virtual Memory: " & My.Computer.Info.AvailableVirtualMemory.ToString())
Console.WriteLine("Available Physical Memory: " & My.Computer.Info.AvailablePhysicalMemory.ToString())
Console.WriteLine("Network Available: " & My.Computer.Network.IsAvailable.ToString())
Console.WriteLine("Additional Info:")
Console.WriteLine("Scroll Lock " & My.Computer.Keyboard.ScrollLock)
Console.WriteLine("Num Lock " & My.Computer.Keyboard.NumLock)
Console.WriteLine("Caps Lock " & My.Computer.Keyboard.CapsLock)
Console.WriteLine("")
Console.WriteLine("Current Processes: ")
Console.WriteLine("")
For Each p As Process In Process.GetProcesses
Console.WriteLine(p)
Next
Console.ReadKey()
End Sub
End Module
Public Class WMI
Private objOS As Management.ManagementObjectSearcher
Private objCS As Management.ManagementObjectSearcher
Private objMgmt As Management.ManagementObject
Private m_strComputerName As String
Private m_strManufacturer As String
Private m_StrModel As String
Private m_strOSName As String
Private m_strOSVersion As String
Private m_strSystemType As String
Private m_strTPM As String
Private m_strWindowsDir As String
Public Sub New()
objOS = New Management.ManagementObjectSearcher("SELECT * FROM Win32_OperatingSystem")
objCS = New Management.ManagementObjectSearcher("SELECT * FROM Win32_ComputerSystem")
For Each objMgmt In objOS.Get
m_strOSName = objMgmt("name").ToString()
m_strOSVersion = objMgmt("version").ToString()
m_strComputerName = objMgmt("csname").ToString()
m_strWindowsDir = objMgmt("windowsdirectory").ToString()
Next
For Each objMgmt In objCS.Get
m_strManufacturer = objMgmt("manufacturer").ToString()
m_StrModel = objMgmt("model").ToString()
m_strSystemType = objMgmt("systemtype").ToString
m_strTPM = objMgmt("totalphysicalmemory").ToString()
Next
End Sub
Public ReadOnly Property ComputerName()
Get
ComputerName = m_strComputerName
End Get
End Property
Public ReadOnly Property Manufacturer()
Get
Manufacturer = m_strManufacturer
End Get
End Property
Public ReadOnly Property Model()
Get
Model = m_StrModel
End Get
End Property
Public ReadOnly Property OsName()
Get
OsName = m_strOSName
End Get
End Property
Public ReadOnly Property OSVersion()
Get
OSVersion = m_strOSVersion
End Get
End Property
Public ReadOnly Property SystemType()
Get
SystemType = m_strSystemType
End Get
End Property
Public ReadOnly Property TotalPhysicalMemory()
Get
TotalPhysicalMemory = m_strTPM
End Get
End Property
Public ReadOnly Property WindowsDirectory()
Get
WindowsDirectory = m_strWindowsDir
End Get
End Property
End Class
MapQuest has changed their XML file and I'm no longer able to retrieve the distance and the time.
My VBA script is hung up on the following:
Set req = New MSXML2.XMLHTTP
req.Open "GET", url, False
req.send
resp = req.responseText
Set xdoc = req.responseXML
refMiles = CSng(xdoc.SelectSingleNode("response / route / distance").Text)
refHours = CLng(xdoc.SelectSingleNode("response / route / time").Text) / 60 / 60
Does anyone have an idea how I can correct this?
Because I am working with a very large sheet of excel and i need to find in a specific column the last 1 before changing to 0.
is there a way to search for that ? how to search for two consecutive cells that have 2 different values ?
I am trying to calculate 2 values. Current Streak and Long Streak.
each record is on 1 row and contains a name and values
each of those columns has a value from 1 to 200. Example:
John Doe 14 16 25 18 40 65 101 85 14 19 18 9 3
Jane Doe 24 22 18 5 8 22 17 17 15 2 1 5 22
Jim Doe 40 72 66 29 25 28
Jan Doe 27 82 22 17 18 9 6 7 9 13
For each row, I'm trying to find the "current" streak and "longest" streak. The values have to be <= 24 to be counted. Data goes left to right.
John: Current 2; Long 5
Jane: Current 13; Long 13
Jim: Current 0; Long 0
Jan: Current 0; Long 8
What would be a formula to calculate the current and long in their own cell on that same row (would have to go before data)?
I have a question. The dutch forum coudn't help me out, so i'm posting this here.
I have a protected Excel-sheet with a table in it. Users can add data to the table and insert and deletes rows.
I have already a working VBA to insert (and delete) a row. But i won't copy the formula from the cell above (this case J24).
screenshot: screenshot
The VBA is:
Private Sub CommandButton1_Click() ActiveSheet.Unprotect Password:="password" ActiveSheet.ListObjects("Tabel2").ListRows.Add AlwaysInsert:=True ActiveSheet.Protect Password:="password" End Sub
Private Sub CommandButton2_Click() ActiveSheet.Unprotect Password:="password" ActiveSheet.ListObjects("Tabel2").ListRows(ActiveSheet.ListObjects("Tabel2").ListRows.Count).Delete ActiveSheet.Protect Password:="password" End Sub
What do I have to add to make it work?
Could some-one help me?
Many thanks!!!!
In the VBA (Macro) below I attempted to combine two macros. When the code runs (triggered by receiving an email) I get a expected End Sub error at line 9.
The first macro(saveAttachtoAccess) saves a file that is attached to an email and is triggered by a rule in Outlook. The second Macro (run_Excel_Macro) would open an excel file, refresh two sheets, save, and then exit.
As stated at line 9 (Exit Sub) I get a Compile error: Expected End Sub. My problem is when End Sub is in line 9 it only runs saveAttachtoAccess.
Public Sub saveAttachtoAccess(itm As Outlook.MailItem)
Dim objAtt As Outlook.Attachment
Dim saveFolder As String
saveFolder = "C:\Users\Josh\Documents\Source_Files"
For Each objAtt In itm.Attachments
objAtt.SaveAsFile saveFolder & "\" & objAtt.DisplayName
Set objAtt = Nothing
Next
Exit Sub
Sub run_Excel_Macro()
Dim App As Excel.Application
Dim wkbk As Excel.Workbook
Set App = New Excel.Application
App.Visible = True
Set wkbk = App.Workbooks.Open("C:\Users\Documents\C:\Users\Josh\Documents\Source_Files"JoshExcel.xlsm")
App.OnTime DateAdd("s", 5, Now()), wkbk.Name &"!RefreshCombineSaveExit"
Set App = Nothing
Set wkbk = Nothing
End Sub
I have over a hundred pipe delimited files in .txt format. I would like to convert them to .csv files. Is there anyway to automate this task. I can program in python , but not in vbs.
I have over a hundred pipe delimited files in .txt format. I would like to convert them to .csv files. Is there anyway to automate this task. I can program in python , but not in vbs.
I am trying to import some data from google my.maps into excel for analysis.
I have "date visited" as a custom field which I enter in to the map over time. I want to export the map data to look at this field specifically.
When I export the data from google it appears that it automatically reformats this data to MM/DD/YYYY and then to MMM-YY. I am in the UK and adding dates as DD/MM/YY so this is intensely frustrating.
I would like to "invert" the date fields to get the correct date out.
The date is in a mixed column with many other integer values so I can't simply convert the column to text and then to DD/MM/YYYY.
To complicate further, dates with the first value above 12 (eg 17/02/15) are stored as text strings.
Sample data: http://ift.tt/18uBQGR xlsx
My company has an issue with Outlook when it is triggered to open by Excel that causes Outlook to hang indefinitely when loading (an issue which cannot be easily fixed). We have a few workbooks with custom ribbons where we now check that Outlook is open first before allowing the user to send via email. The problem is that this doesn't account for users that still use the File > Save & Send > Send as Attachment.
I would like to know if anybody knows about a process by which I can add on a routine to the existing button that we could embed in everyone's Excel that checks first if Outlook is open? I have the Outlook check written so I'm just needing help with finding a way to run it.
Any help is appreciated.
I have an ActiveX Button on my Excel Spreadsheet. When I click the button the "box" appears to shift right while the code runs. After the code has finished running the box reasserts itself in its original spot on the spreadsheet. Does anyone know why it's doing this? Have you had this problem before? I tried using Format Control to allow it to move on the spreadsheet and/or lock on the spreadsheet, but it is still exhibiting the same behavior.
Thanks.
Im trying to come up a formula to analyze some data. Basically, I need a formula to figure out:
if cell G7 is greater then 45 but less than 50 then add 5. Can someone help me out with this? Thanks you!
I am creating a XML from a string (This string is a value of another XML).
I tried simplexml_load_string to create.
My Scenerio:
My UI is in php and I am sending Excel sheet data through PHPExcel to my project's services part. It returns:
<?xml version=\"1.0\" encoding=\"UTF-8\" standalone=\"no\"?>
<root>
<searcheddata>
{Account0={AccountName=1, AddressLine2=0.0, Country=0.0, CellPhone=0.0, City=0.0, WorkPhone=0.0, Other=0.0, HomePhone=0.0, Email=0.0, AddressLine1=0.0, State=0.0, Zip=0.0, AlternateEmail=0.0}, City1={City=1, AddressLine2=0.0, Country=0.0, CellPhone=0.0, WorkPhone=0.0, Other=0.0, HomePhone=0.0, Email=0.0, AddressLine1=0.0, State=0.0, Zip=0.0, AlternateEmail=0.0}, State2={State=1, AddressLine2=0.0, Country=0.0, CellPhone=0.0, City=0.0, WorkPhone=0.0, Other=0.0, HomePhone=0.0, Email=0.0, AddressLine1=0.0, Zip=0.0, AlternateEmail=0.0}, Country3={Country=1, AddressLine2=0.0, CellPhone=0.0, City=0.0, WorkPhone=0.0, Other=0.0, HomePhone=0.0, Email=0.0, AddressLine1=0.0, State=0.0, Zip=0.0, AlternateEmail=0.0}}
</searcheddata>
</root>
Now I trying to create XML from the value of <searcheddata>.
Atlast I need a XML as:
<xml>
<root>
<Account0>
<AccountName>1</AccountName>
<AddressLine2>0.0</AddressLine2>
<Country>0.0</Country>
<CellPhone>0.0</CellPhone>
<City>0.0</City>
<WorkPhone>0.0</WorkPhone>
<Other>0.0</Other>
<HomePhone>0.0</HomePhone>
<AddressLine1>0.0</AddressLine1>
<State>0.0</State>
<Zip>0.0</Zip>
<AlternateEmail>0.0</AlternateEmail>
</Account0>
<City1>
<City>1</City>
<AddressLine2>0.0</AddressLine2>
<Country>0.0</Country>
<CellPhone>0.0</CellPhone>
<WorkPhone>0.0</WorkPhone>
<Other>0.0</Other>
<HomePhone>0.0</HomePhone>
<AddressLine1>0.0</AddressLine1>
<State>0.0</State>
<Zip>0.0</Zip>
<AlternateEmail>0.0</AlternateEmail>
</City1>
</root>
</xml>
What I tried:
<?php
$str = "<?xml version=\"1.0\" encoding=\"UTF-8\" standalone=\"no\"?><root><searcheddata>{Account0={AccountName=1, AddressLine2=0.0, Country=0.0, CellPhone=0.0, City=0.0, WorkPhone=0.0, Other=0.0, HomePhone=0.0, Email=0.0, AddressLine1=0.0, State=0.0, Zip=0.0, AlternateEmail=0.0}, City1={City=1, AddressLine2=0.0, Country=0.0, CellPhone=0.0, WorkPhone=0.0, Other=0.0, HomePhone=0.0, Email=0.0, AddressLine1=0.0, State=0.0, Zip=0.0, AlternateEmail=0.0}, State2={State=1, AddressLine2=0.0, Country=0.0, CellPhone=0.0, City=0.0, WorkPhone=0.0, Other=0.0, HomePhone=0.0, Email=0.0, AddressLine1=0.0, Zip=0.0, AlternateEmail=0.0}, Country3={Country=1, AddressLine2=0.0, CellPhone=0.0, City=0.0, WorkPhone=0.0, Other=0.0, HomePhone=0.0, Email=0.0, AddressLine1=0.0, State=0.0, Zip=0.0, AlternateEmail=0.0}}</searcheddata></root>";
$a = array();
$name = array();
$values = array();
$mainArray = array();
$xml = simplexml_load_string($str);
$s3 = $xml->searcheddata;
$pieces = (explode("},", $s3));
$pieces[0] = substr($pieces[0], 1);
$length = sizeof($pieces);
$pieces[$length - 1] = substr($pieces[$length - 1], 0, -2);
foreach ($pieces as $value) {
$morePiece = (explode("={", $value));
for ($i = 0; $i < sizeof($morePiece); $i = $i + 2) {
$lastPiece = explode(", ", $morePiece[$i]);
for ($j = 0; $j < sizeof($lastPiece); $j++) {
array_push($a, $lastPiece[0]);
}
}
for ($i = 1; $i < sizeof($morePiece); $i = $i + 2) {
$lastPiece2 = explode(", ", $morePiece[$i]);
for ($j = 0; $j < sizeof($lastPiece2); $j++) {
$finalPiece2 = explode("=", $lastPiece2[$j]);
for ($k = 0; $k < sizeof($finalPiece2); $k++) {
if ($k % 2 == 0) {
array_push($name, $finalPiece2[$k]);
} else {
array_push($values, $finalPiece2[$k]);
}
}
}
}
}
echo sizeof($pieces)."<br>";
print_r($a);
echo '<br><br>';
print_r($name);
echo '<br><br>';
print_r($values);
echo '<br><br>';
?>
I was trying to put all my key and value in respective array and then to create a XML using simplexml-load-string.
Suggestions are welcome.
The title may be as vage as possible, but i'm new at this and i'm trying.
I want have a sheet with the setup:
User Role | Permission
But the a permission could also apply for multiple User roles,
So i want to create a more user friendly sheet Where i plot:
User role x | User role y| User role z
Permission 1
Permission 2
Permission x
And want to display this to true or false, or something.
But i am kind of stuck because my wildest VLOOKUP is just a lookup for 1 value in another sheet. i also saw some form of match and index combined, maybe that's something that could work?
Could you guys help me out with this?
I have this range it selects all values in that column:
Set c = ThisWorkbook.Worksheets("SplitSkillInterv").Range("C3:C" & Rows.Count)
Now I need to make it to find some value and then its row (it could be 1 or 10+, so I need to find first one) any ideas?
I need to find for example: in range c values are from 1 to 10 and randomly repeating till total 100 rows. I and lets say i need to find when first time will be number 3 in range and its row.
I have a program which receives .xlsx (Excel file) as an input.
Somehow I manage to create some, lets say, "strange empty state" in one of the cells crucial for work of my program. By that, I mean that cell looks perfectly empty and cause program to has behavior A.
However, If I mark that disputable cell, press "delete" button (try to empty content) from keyboard upon it and save document, program has different behavior B.
Any idea which value could be initially in the cell which looks perfectly empty? And how is possible that simple deleting changes content of the cell? Any idea what to do?
Q1. From above example you can see I have some numbers from row 34 onwards, using VBA I would like to write a method that will take a value from column B34 (which is K15), it will go to K15 cell and replace 0 with whatever is in B36 (which is 0). Next it will look into cell C34 (which is K12) the program will go to K12 and insert whatever is in D36 (which is 80). This is where first episode ends.
In next method the program should perform same step as above, but this time the program will look in B37 (which is E7) go to E7 insert whatever is in B39 (which is 0) then go to C37(which is W1) go to W1 and insert whatever is in D39. This is second episode done. IMPORTANT- the program should first insert values from first episode and then insert values from second episode because based on first episode values might change in second episode.
Q2. Another question I would like to ask is: above shows two episodes, I would be running this 200 times is there any easy way of doing this? rather than writing 200 methods (each method per episode) assuming that the program will look into fixed rows and columns. For example in episode 1 the program looks at B34 & C34 and in episode 2 the program looks at B37&C37 so from this we know there is a gap of 3 rows 37-34=3. So values from every 3 rows down in B and C columns will be used to find the coordinate to insert some values from other cell.
I would like to have two separate answers one for question 1 and another for Q2 so I can get a better understanding of this:
I have Sheet 1(Year) and Sheet 2 (January). What I want to do is, if Cell H4-H7 on Sheet 2(January) contain any of 11 possible words e.x.(San Francisco, Napa, Novato, Stockton....) it will abbreviate the word to Cell B3 on Sheet 1(year) SO if "San Francisco would read SF". If nothing in those cells then it would remain blank.
I have a problem with starting the Excel Application under a particular user.
I try to schedule this script (C#) through an application X (not Windows Task Scheduler. And this application will always use a service account to run services on the server). If I run the C# script in command prompt under the same user, it runs. Under the application X, which uses the exact same user, to initiate the C# script, it fails to open the Excel application (not sufficient permission?).
This script calls: app.Workbooks.Open(ExcelFileName,0,false,Type.missing....), yet it gives the following error:
Microsoft Excel cannot access the file "...". There are several possible reasons:
-The file name or path does not exist.
-The file is being used by another program.
-The workbook you are trying to save has the same name as a currently open workbook.
I tried all the methods that I found online to no avail.
Yet, still the same error. I think it's permission but I am not sure where and what to do for this to work.
Now, normally, when I run this excel report, I can double-click on the file and it'd automatically run, save the new parameters into the current file and generate a new excel file (with date attached to the file name). That means there is a change (save) to the original file.
I appreciate all your help!
I am trying to find the last cell in a column that is populated with data in VBA. The problem is, in the range, there are blank cells. Is there any way to select the last cell with data if there are blanks? Any help would be greatly appreciated! I have pasted my range definitions below.
If Range("BL2") <> "" Or Range("BM2") <> "" Then
Set usr11 = Range("BL:BL")
Set usr12 = Range("BM:BM")
I would like to create an algorythm generating all permutations of a set number and writing it onto an Excel spreadsheet. For example: If I give it n=8 (1,2,3,4,5,6,7,8) and p=3, it will generates
1,1,1
1,1,2
1,1,3
....
1,2,1
1,2,2
...
I've heard about recursive functions but I'm not good enough to use it. I would like something like this but generationg all permutations: Combination Algorithm in Excel VBA Thanks for your help
EDIT: I've finally found it on my own. I just have to put this in my main worksheet:
1 8
1 8
1 8
It is possible to put differents start and end for each variable and it's possible to put as many variable as I want. Here is my solution:
Public Sub ListerPermutations()
Dim tableauParam() As Double
Dim p As Integer
Dim j As Integer
j = 0
p = 0
p = Application.WorksheetFunction.CountA(Feuil1.Range("$A:$A"))
ReDim tableauParam(p, 2)
For j = 1 To p
tableauParam(j, 1) = Worksheets("Feuil1").Range("A" & j).Value
tableauParam(j, 2) = Worksheets("Feuil1").Range("B" & j).Value
Next
Dim n As Integer
n = 0
For j = 1 To p
If n = 0 Then
n = (tableauParam(j, 2) - tableauParam(j, 1) + 1)
Else
n = n * (tableauParam(j, 2) - tableauParam(j, 1) + 1)
End If
Next
Worksheets("Feuil1").Range("H1").Value = n
Dim i As Integer
For j = 1 To p
Worksheets("Feuil1").Range(Chr(68 + j) & 1).Value = tableauParam(j, 1)
Next
For j = 1 To n - 1
If Worksheets("Feuil1").Range("E" & j).Value = tableauParam(1, 2) Then
Worksheets("Feuil1").Range("E" & j + 1).Value = tableauParam(1, 1)
Else
Worksheets("Feuil1").Range("E" & j + 1).Value = Worksheets("Feuil1").Range("E" & j).Value + 1
End If
Next
For j = 2 To p
For i = 2 To n
If recursive(i, j, tableauParam) Then 'Teste si c'est la valeur max sur tous les indice précédents
If Worksheets("Feuil1").Range(Chr(68 + j) & i - 1).Value = tableauParam(j, 2) Then
Worksheets("Feuil1").Range(Chr(68 + j) & i).Value = tableauParam(j, 1)
Else
Worksheets("Feuil1").Range(Chr(68 + j) & i).Value = Worksheets("Feuil1").Range(Chr(68 + j) & i - 1).Value + 1 'case(i,j)=case(i-1,j)+1
End If
Else
Worksheets("Feuil1").Range(Chr(68 + j) & i).Value = Worksheets("Feuil1").Range(Chr(68 + j) & i - 1).Value
End If
Next
Next
End Sub
Function recursive(k As Integer, l As Integer, tableauParam) As Boolean
If l > 1 Then
If Worksheets("Feuil1").Range(Chr(68 + l - 1) & k - 1).Value = tableauParam(l - 1, 2) Then
recursive = recursive(k, l - 1, tableauParam)
Else
recursive = False
End If
Else
recursive = True
End If
End Function
I have a Table object, and I need a mapping from the general column references in the document (that is: A, B, C, ...) to the corresponding table columns names (for example: Unity, Totals, ...).
For example having a Table with 2 columns ("Id", "Total") that begin at cell 'A1'. Note that the numbers of row doesn't matter at all. The resultant mapping should be:
How can I accomplish this task with the C# open xml sdk 2.5 ?
With talend I'm reading from a tSalesforceInput and exporting to a tFileOutputExcel. Inbetween there is a tMap just passing through all fields.
In a second step I want to use the locally written xlsx file for an tFileInputExcel and use it as a Lookup for a tMap.
I perfectly works - as long I avoid Date fields. Once I add one datefield to the schema, I'm getting many line of error-output like this:
...
The cell format is not Date in ( Row. 1 and ColumnNum. 13 )
The cell format is not Date in ( Row. 1 and ColumnNum. 13 )
The cell format is not Date in ( Row. 1 and ColumnNum. 13 )
The Salesforce Date Pattern is "yyyy-MM-dd'T'HH:mm:ss'.000Z'" and its used consistently at all schemas.
Any idea on how to reimport dates in this format? Is it because Excel it treating it not as Date but as String? Possibly a CSV would help, but I would prefer not using a CSV, because it's hard to deal with if you are using a German locale...
I have an excel sheet with 500,000 rows. It has 3 columns in the below format
Staff Locations Roles
1 Location1 Role1
1 Location2 Role1
2 Location2 Role2
3 Location3 Role3
3 Location3 Role4
The output comes in the below format
Staff Locations Roles
1 Location1, Location2 Role1
2 Location2 Role2
3 Location3 Role3
3 Location3 Role4
I have the below vbscript I am using that works if the number of rows is small.
Sub Sort_Duplicates()
Dim lngRow, lngRow2 As Long
With ActiveSheet
Dim flag As Integer: flag = 0
Dim i As Integer
Dim columnToMatch As Integer: columnToMatch = 1
Dim column2ToMatch As Integer: column2ToMatch = 3
Dim columnToConcatenate As Integer: columnToConcatenate = 2
lngRow = .Cells(538537, columnToMatch).End(xlUp).Row
.Cells(columnToMatch).CurrentRegion.Sort key1:=.Cells(columnToMatch), Header:=xlYes
Do
If .Cells(lngRow, columnToMatch) = .Cells(lngRow - 1, columnToMatch) Then
'flag = 1
i = 1
lngRow2 = lngRow
Do While Cells(lngRow2, columnToMatch) = .Cells(lngRow2 - i, columnToMatch)
If .Cells(lngRow2, column2ToMatch) = .Cells(lngRow2 - i, column2ToMatch) Then
.Cells(lngRow2, columnToConcatenate) = .Cells(lngRow2, columnToConcatenate) & ", " & .Cells(lngRow2 - i, columnToConcatenate)
.Rows(lngRow2 - i).Delete
End If
i = i + 1
Loop
lngRow2 = lngRow2 - 1
End If
' If flag = 1 Then
' lngRow = lngRow2
' flag = 0
' Else
lngRow = lngRow - 1
'End If
Loop Until lngRow = 1
End With
End Sub
When I try it for the sheet with 500,000 rows, it starts sorting but it throws a popup error "Overflow" with no additional messages. I tried to do this with 90,000 and still the same. Any suggestion how I can fix this issue?
Thanks in advance
For Excel 2013, please...
Here's what they look like (I'll just mockup superhero names and corresponding values):
I got old/existing data in Sheet1, hence:
Sheet1
Column A Column B
Daredevil /me want output here/
Spider-Man /output/
Cyclops /output/
Wolverine /output/
Spider-Woman /output/
Lockheed /output/
Warpath /output/
I got new data in Sheet2, hence:
Sheet2
A Column B Column C
Lockheed Violet
The Streak Red
Cyclops Blue
Warpath Black
Wolverine Yellow
Daredevil Red
Kick-Puncher Red
Havok Violet
If there's no match, I want it to display: "whatevs". Below is a sample output I'm aiming for:
Sheet1
Column A Column B
Daredevil Red
Spider-Man whatevs
Cyclops Blue
Wolverine Yellow
Spider-Woman whatevs
Lockheed Violet
Warpath Black
I've wasted two working days at this already :'( After that, I've decided I'll let the experts handle this. I can't!
Thank you
I would like to add the returns of a json to excel cell by cell, like
2003-1 2003-2 2003-3 2003-4 2003-5 2003-6 2003-7 2003-8 2003-9 ....
number number number number number number number number number ....
Here is my code, but as can be understood it only pastes to the first cell, I could not construct the system I wanted. Thank you in advance..
from xlwings import Workbook, Sheet, Range, Chart
import requests
import json
payload = {'cityId':3969, 'lbDistricts':599, 'criter':149,'startdate':'2003-01','cmd':'result','areaCode':18439}
url = "http://ift.tt/1MUARz5"
r = requests.post(url, data=payload)
wb = Workbook()
data = json.loads(r.text)
data = map(dict.values, data[u'output'][u'resultset'][u'record'][u'data'])
for row in data:
Range("A1").value = '{:10}{:10}'.format(*row)
Im having an addon for Excel which requires my company's server to open. However if I want to open Excel from home at my work computer, it'll crash due to said addon not having access to the Company server.
Is it possible to disabvle the addon without having to open Excel?
I currently have the following formula to track my work schedule. Time is put down in traditional format ie. 8:00 am, and is then totaled to show hours worked as a whole number 8, for 8 hrs worked and.. Below is the formula.
=IF(D2="","",ROUND(IF(OR(C2="",B2=""),(D2-A2),(B2-A2)+(D2-C2))*24*4,0)/4)
A=Time In , B=Lunch Out , C=Lunch In , D=Time Out.
I would like to alter this to in two ways
1st. Remove the ROUND function, I don't need it.
2nd. I don't need Lunch In and Out, I need column B to =Lunch, displayed as a whole number, 1 for one hour or, .5 for half hour. Which is subtracted from Time In and Time Out since we're not paid for lunch..
Thanks for any help, I'm totally lost on it, VERY new to excel and couldn't figure this out..
Does JS support Excel chartobjects. I found a reference link to use, but when I try, I get Object doesnt support this property or method.
Reference Link:http://ift.tt/1E2mvua
Error Line: var chartObj = Sheet.ChartObjects.Add(chartLeftPixels, chartTopPixels, chartWidthPixels, chartHeightPixels);
I am trying to populate a list box with the a list of dynamic length from another sheet. I want to eventually be able to select multiple items from this list box to trigger some vlookups and further calculations.
However, I am unsure of how to populate the listbox with my list. When I run/step through the following code I do not get any error messages but it does not populate the box
Sub FilterUniqueData()
Dim Lrow As Long, test As New Collection
Dim Value As Variant, temp() As Variant
ReDim temp(0)
Dim Value1 As Variant
On Error Resume Next
With Workbooks("Copy of SWR1304 (Future Development Risk Assessment) Strathaven.xls").Sheets("Non Household Metered Users")
Lrow = .Range("h" & Rows.Count).End(xlUp).Row
temp = .Range("h12:h" & Lrow).Value
End With
For Each Value In temp
If Len(Value) > 0 Then test.Add Value, CStr(Value)
Next Value
ReDim temp(0)
Workbooks("DMA_metered_tool").Worksheets("DMA list").Shapes("List Box 2").ControlFormat.RemoveAllItems
For Each Value In test
Worksheets("DMA list").Shapes("List Box 2").ControlFormat.AddItem Value
Next Value
Set test = Nothing
End Sub
Any suggestions much appreciated.
Cheers
I am not sure whether it is possible or not, but I believe the issue is worth asking. To cut it short, here is what I would like to achieve to avoid the use of a new column (group):
=MATCH(1;(B=A:A)*({"criteria5","criteria6"}=columnB);0)
Thank you for your feedback.
I'm at my wits end trying to troubleshoot COUNTIF
I see there are three instances of 11.0.6000.0162 but =COUNTIF(A1:A8559,B1) doesn't believe this.
And I evaluated the cells
=A1=B7 = TRUE
=A2=B7 = TRUE
=A5=B7 = TRUE
I have been trying to run larger figures and now I know why the numbers don't quite add up .... Thanks!
The following is a visualization of the demanded in a simplified example:
What I need:
I need a formula that can give me the results in range A15:D16, on behalf of the following information:
Information:
There must be, and can only be, one of each type.
The priority of the formula must be:
- Constraint 1
- Constraint 2
However the secondary constraint must be fulfilled.
Power Query Version 2.17 is supporting SSAS Cubes as data source now. When I connect to a cube I just see the standard measures of the cube and not the calculated members defined in the cube. Is there a way in Power Query to get access to the calculated members?
I have data like following:
A 2015-02-26 0
B 2015-02-26 1
C 2015-02-26 2
D 2015-02-26 3
A 2015-02-27 4
B 2015-02-27 5
C 2015-02-27 6
D 2015-02-27 7
I would like to have matrix like this:
2015-02-26 2015-02-27
A 0 4
B 1 5
C 2 6
D 3 7
Using pivot table I only receive:
2015-02-26 2015-02-26 2015-02-26 2015-02-26 [....]
A 0
B 1
C 2
D 3
[...]
Of course I am trying to do this in Excel (mine version is 2013)
I have an excel sheet (Sheet 1) which have data from day 1 to end of the month in two columns (column A & B). In sheet 2 i am maintaining the sum of each day, ie column A+B from sheet 1. So in sheet 2 i have formula in each row to add column A+B from sheet 1. I need to fetch the last displayed (or last entry) non-zero value from this column in sheet 2 which contains the formula and display it in another cell in sheet 2.
How do I take the Macro from my xslm file and apply it to my xlsx file?
import cx_Oracle
import xlsxwriter
import win32com.client
SQL = "Select * FROM TABLE"
cursor1 = con.cursor()
cursor1.execute(SQL)
workbook = xlsxwriter.Workbook('OUTPUT.xlsx')
worksheet = workbook.add_worksheet('Summary - Attendee')
worksheet.set_tab_color('red')
for i, row in enumerate(cursor1):
for j, col in enumerate(row):
worksheet.write(i+1,j,col)
excel = win32com.client.Dispatch('Excel.Application')
excel.Visible = 1
excel.Workbooks.Open(Filename="Path\Macro.xlsm")
excel.Application.Run("Format")
excel.Workbooks(1).Close(SaveChanges=1)
excel.Application.Quit()
excel = 0
workbook.close()
Macro.xlsm VBA code:
Sub Format()
Sheet1.Select
Cells.Select
Cells.EntireColumn.Autofit
End Sub
Both of these work but as separate processes and files. I want to call the Macro from xlsm and apply it into my xlsx file to format my xlsx file. Any ideas?
I need to automatically calculate the number of calendar days between two dates for individual months
EG:
Start End Jan2014 Feb2014 Mar2014 Apr2014 May2014
18/11/2013 9/03/2014 31 28 9 0 0
7/04/2014 18/05/2014 0 0 0 23 18
....
I have this function working ( It returns the row where the text DK001 sits in the ID range)
Found = Application.Evaluate("=IF(ID=""DK001"",ROW(ID),""x"")")
I would like to feed the searchcriteria (e.g. DK001) as a string, like
Found = Application.Evaluate("=IF(ID=SearchString,ROW(ID),""x"")")
I fail in creating a string that is accepted as a search criteria. I need your help on this! What am I doing wrong?
According to a couple of web sites, SUMIFS and COUNTIFS are faster than SUMPRODUCT (for example: http://ift.tt/1MUN6M1). I have a worksheet with an unknown number of rows (around 200 000) and I'm calculating performance reports with the numbers. I have over 6000 times almost identical SUMPRODUCT formulas with a couple of difference each times (only the conditions change).
Here is an example of what I got:
=IF(AFO4>0,
(SUMPRODUCT((Sheet1!$N:$N=$A4)
*(LEFT(Sheet1!$H:$H,2)="1A")
*(Sheet1!$M:$M<>"service catalog")
*(Sheet1!$J:$J="incident")
*(Sheet1!$I:$I<>"self-serve")
*(Sheet1!$AK:$AK=AFM$1)
*(Sheet1!$E:$E>=$E$1)
*(Sheet1!$E:$E<$E$2))
+SUMPRODUCT((Sheet1!$AJ:$AJ=$C4)
*(LEFT(Sheet1!$H:$H,2)="1A")
*(Sheet1!$M:$M<>"service catalog")
*(Sheet1!$J:$J="incident")
*(Sheet1!$I:$I="self-serve")
*(Sheet1!$AK:$AK=AFM$1)
*(Sheet1!$E:$E>=$E$1)
*(Sheet1!$E:$E<$E$2)))/AFO4,0)
Calculating that thing takes a little bit more than 1 second. Since I have more than 6000 of those formulas, it takes a little bit over an hour to calculate everything.
So, I'm now looking at how I could optimize that formula. Could I convert it to SUMIFS? Would it be faster? All I'm adding up here is 0s and 1s, I'm just counting the number of rows in my data source (Sheet1) where the set of conditions is met. Maybe COUNTIFS would work better?
I would appreciate any help to gain some execution time since we need to execute the formulas every month.
I can use VBA if that helps, but I always heard that Excel formulas were usually faster.
I have an array which I populate with Varibles from an excel table. I then use an each loop to cycle through this array. The allocation of the array lines up with some cell I would like to populate.
'arRow is a Dynamic Array, that varies in size
For each vIndex in arRow
if vIndex = 0 then
'do nothing
else
'Populate corisponding cell
Cells(2, ???).value = vIndex
end if
next vindex
How would I find the index for the Each-Loop?
I have another code thats running in the background, and when certain conditions are met it will call the sub Alert. There are multiple scenarios that call this code out though and whats happening right now is Alert starts running more frequently then the 3 seconds I would like it to (indicated by AlertTIMER). Here's my attempt to solve the issue
Sub Alert()
If Sheets("Sheet1").CommandButton21.Enabled = False And Sheets("Sheet1").CommandButton22.Enabled = False And Sheets("Sheet1").CommandButton25.Enabled = False Then
Beep
Application.Speech.Speak ("Part is done")
Beep
Call AlertTIMER
Exit Sub
ElseIf Sheets("Sheet1").CommandButton21.Enabled = False And Sheets("Sheet1").CommandButton22.Enabled = False Then
Beep
Application.Speech.Speak ("Part is done")
Beep
Call AlertTIMER
Exit Sub
ElseIf Sheets("Sheet1").CommandButton21.Enabled = False And Sheets("Sheet1").CommandButton25.Enabled = False Then
Beep
Application.Speech.Speak ("Part is done")
Beep
Call AlertTIMER
Exit Sub
ElseIf Sheets("Sheet1").CommandButton22.Enabled = False And Sheets("Sheet1").CommandButton25.Enabled = False Then
Beep
Application.Speech.Speak ("Part is done")
Beep
Call AlertTIMER
Exit Sub
ElseIf Sheets("Sheet1").CommandButton21.Enabled = False Then
Beep
Application.Speech.Speak ("Part is done")
Beep
Call AlertTIMER
Exit Sub
ElseIf Sheets("Sheet1").CommandButton22.Enabled = False Then
Beep
Application.Speech.Speak ("Part is done")
Beep
Call AlertTIMER
Exit Sub
ElseIf Sheets("Sheet1").CommandButton25.Enabled = False Then
Beep
Application.Speech.Speak ("Part is done")
Beep
Call AlertTIMER
Exit Sub
Else
Exit Sub
End If
End Sub
Sub AlertTIMER()
Dim ACountDown As Date
ACountDown = Now + TimeValue("00:00:03")
Application.OnTime ACountDown, "Alert"
End Sub
Clearly it did not work haha. Originally i had one if Statement with or's instead of the ands... but that was not working either. Any help?
EDIT:
I believe what is happening is in the AlertTIMER sub it calculates the value ACountDown. In example lets say it is first triggered at 12:00:00 AM ( ACountDown will = 12:00:03 AM) and then submts it to Application.OnTime as that value (12:00:03 AM). When another event is triggered lets say at 12:00:01 AM it calculates ACountDown as 12:00:04 AM and then submits it to Application.OnTime as that value (12:00:04 AM). So in the end I have "Alert" being triggered at 12:00:03 and 12:00:04. One way to solve this would be if the code kept ACountDown as a variable until "Alert" is triggered from Application.OnTime??. So with the previous example it calculates 12:00:03 AM first for ACountDown, but then is reset to 12:00:04 AM when the second trigger comes in. At 12:00:04 AM it then triggers "Alert" via Application.OnTime - only running it once.
EDIT2:
Another way to work around this, if there is a way to accomplish this, would be to check if there is an Application.OnTime running/active if so then cancel it. The code would look like this:
Sub AlertTIMER()
Dim ACountDown As Date
If Application.OnTime(, "Alert") Is Nothing Then
Else
Application.OnTime ACountDown, "Alert", , False
End If
ACountDown = Now + TimeValue("00:00:03")
Application.OnTime ACountDown, "Alert"
End Sub
But If Application.OnTime(, "Alert") Is Nothing Then returns an error... as you can't check to see if it is nothing since it's not an object. Any thought on how maybe I could get this to work?
I want to ask
I have a very large csv files, with about 130.000 records. But I'm little bit confused, that a different on rows number when I open it on microsoft excel and text editor like notepad++, or sublime text. For example if I open the csv on excel, it have "x" number of rows and if I open the csv file on text editor, it have "y" number of rows. X and Y are not have a big gap. But the x is less than y.
Do you know why like this happened? Thanks all.....
Ok, So at school, we have a maths test thing, called Rainbow Maths. As I have completed it; I was asked to make some more. How ever, every class needs different questions. I decided to make a Rainbow maths genarator so every class can run a macro; and create there times table/ maths questions.
Ok. So what I would like to do is make a random selector. I can have a list in my code and it can randomly select one of the x amount of items in the list. I have worked out how to do the rest; its just this random selector. I am using Excel 2010. Thanks -
Sodex
I am designing a production schedule to accept data from 3 databases. Production, Marketing and Warehouse inventory control. Im using VBA and have completed all the necessary data collection and analysis.
I am using simple graphs to plot the data and i would like to distribute various combinations of tables and graphs on seperate pages of a pdf document to complete a production report.
The best code i have to do this, submitted by sineil, is,
saveLocation = Application.GetSaveAsFilename( _
fileFilter:="PDF Files (*.pdf), *.pdf")
If saveLocation <> "False" Then
ActiveSheet.Shapes.Range(Array("Chart 2", "Chart 7", "Chart 8", "Chart 11", _
"Chart 6", "Chart 5", "Chart 12")).Select
ActiveSheet.ExportAsFixedFormat xlTypePDF, saveLocation, xlQualityStandard, False, False, 1, 2
The above code saves everything on the worksheet in a scale that is not desirable.
Please assist.
Thanks, Neel
I am importing data from excel to database and I am successful in doing that. Is there any way where I can check if the current row exist in database before inserting that particular row from excel. Here is the code which I am using to import data from excel
Thank You.
Excel 2013. This is my PIVOT table where the item labels are in tabular form. What I am trying to do is sorting as follows :
How can I accomplish this? I tried more sort options. No use. Can this be done using excel VBA?
I've got a excel workbook that has a macro written. It takes information from one sheet and pastes it to another. However each month the data range changes. Currently tracking a 12 month roll. So current month is Feb we use data from Feb 2014 - Jan 2015.
That being said there is the current date on both sheets. Is there a way to add to my pre-existing macro to paste it into the correct cells that line up with the correct date range. Or to take the data 12 lines and paste it into the other sheet on the current month range?
Sub Paste_to_UnitProfile()
'
' Paste_to_UnitProfile Macro
'
'
Range("D2:D13").Select
Selection.Copy
ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
Sheets("Unit Profile").Select
Range("G151").Select
ActiveSheet.Paste
End Sub
I'm adding the INDEX/MATCH that I tried to use on this but I'm new to this and I'm sure there's something small that I'm goofing up on:
=INDEX(PS250-'1EngineHours'!D2:D13,MATCH(E151,UnitProfile!G151:G170,0))
I have the below code that adds values to an array if it meets a criteria.
It keeps looping horizontally through the columns across a row and then repeats the same for the next row and so on.
I am trying to clear the values accumulated in the array and empty it at the end of the columns loop:
For a = 21 To 23
Count = 0
For b = 2 To 36
If Not Worksheets("Sheet1").Cells(a, b).Value = "A" And Not Worksheets("Sheet1").Cells(a, b).Value = "B" Then
Count = Count + 1
Else
If Not Count = 0 Then
Dim arr() As Long
arrLen = 1
ReDim Preserve arr(1 To arrLen)
arr(arrLen) = Count
arrLen = arrLen + 1
For i = LBound(arr) To UBound(arr)
msg = msg & arr(i) & vbNewLine
Next i
Count = 0
End If
End If
Next b
MsgBox Worksheets("Sheet1").Cells(a, 1).Value & vbNewLine & msg
Erase arr 'not working
Next a
As you can see from the above code, I get a msgbox displaying the values at the end of each loop, however as it continues, the array keeps getting bigger and bigger indicating that the Erase line is not working.
Kindly help!