excel,vba,excel-vba,excel-2007

You could use a simple UDF: Function MyConcat(ConcatArea As Range) As String For Each x In ConcatArea: xx = IIf(x = "", xx & "", xx & x & "|"): Next MyConcat = Left(xx, Len(xx) - 1) End Function Copy the above code into a standard code module, and use...

vba,macros,excel-2007,increment,select-case

In order for the increment to work, you have to use global variables. Also, your offset leads to the cells being shifted downwards instead of to the right. I altered your code. It will increment the offset and shift it to the right on new macro executions. Dim Count1 As...

excel,excel-formula,excel-2007

Highlight your entire dataset and press "alt" then "a" then "m". That will remove duplicate entries, then a countif(B:B, B1) will count the number of entries for that specific client number. Countif(B:B,1) will work for client 1 Countif(B:B,2) will work for client 2 But if you reference the cells they...

In VBA Edidor set the Property Visible of your sheet to 2 - xlSheetVeryHidden so the user can't see the sheet when the file is opened See also: http://www.excel-university.com/make-an-excel-worksheet-very-hidden...

excel,vba,excel-vba,excel-2010,excel-2007

The error was actually outside of the function, when the function is called. Instead of passing in a collection for DataSet, it passes in nothing.

excel,vba,excel-vba,excel-2007

Perhaps just: Sub Eformat_as_table() With ThisWorkbook.Sheets("format sheet") .ListObjects.Add(SourceType:=xlSrcRange, Source:=.Cells(1).CurrentRegion, _ XlListObjectHasHeaders:=xlYes, TableStyleName:="TableStyleMedium1").Name = "Table2" End With End Sub ...

excel,excel-formula,excel-2007,worksheet-function

If the successive results of RATE do not converge to within 0.0000001 after 20 iterations, RATE returns the #NUM! error value. Without including a guess for the Guess it is assumed to be 10 percent and that is nowhere near the actual rate that would be required to equate...

You can implement a Workbook_SheetChange that will cover all of the worksheets (excluding the first) that will act on a change to a numeric value in columns A. Tap Alt+F11 and when the VBE opens, look to the Project Explorer on the left side. If you cannot find the Project...

sql,excel-vba,ms-access,access-vba,excel-2007

datevalue(Updated_Date) returns a Date/Time value. So compare it to a Date/Time value instead of a string. Use # characters to delimit the Date/Time value. Dim strSelect As String strSelect = "SELECT SID, Requestor, Comments, Updated_Date, Updated_By " & _ "FROM CL WHERE datevalue(Updated_Date) >= " & Format(N, "\#yyyy-m-d\#") rs.Open strSelect,...

excel,vba,excel-vba,excel-2007

You may need to enable the Microsoft Scripting Runtime library on your boss's computer if you haven't already. In some instances this library needs to be enabled in order to interface with the File System Object. This library can be accessed from the Visual Basic Editor by pressing Tools >...

excel,ms-office,excel-2007,behavior

See http://office.microsoft.com/en-gb/excel-help/lookup-function-HP010342671.aspx For the LOOKUP function to work correctly, the data being looked up must be sorted in ascending order. This is not the case with the texts "Dezember", "Januar", "Februar", ... If you would use real dates 01.12.2013, 01.01.2014, 01.02.2014, ... and format them as "MMMM", then it should...

excel,vba,excel-vba,excel-2007,pivot-table

Fun problem! Because you were having problems involving the size of your data, I tried to avoid using objects like dictionaries(I don't know how much a dictionary can hold). Instead I created a program that keeps track of very little data, but ends up continuously reading/writing from the file: It'll...

excel-formula,excel-2007,sumifs

why don't you directly reference that cell without quotes? such as: =SUMIFS(D:D,B:B,sheet1!$A$3,c:c,"=E") ...

excel,excel-2007,conditional-formatting

If the Applies to: is to remain `$E:$E, then the formula should be based on, =AND(ROW()>1, $E1<$F1) The additional ROW()>1 condition is actually not needed for your layout as Total Hours is considered greater than Expected Hours but if the column label in E1 started with a letter less...

excel,excel-formula,excel-2007

Here is the turkish version. =SOLDAN(METNEÇEVİR(A1,"#,##0 "),BUL(",",METNEÇEVİR(A1,"#,##0 "))-1) & "." & PARÇAAL(METNEÇEVİR(A1,"#,##0 "),BUL(",",METNEÇEVİR(A1,"#,##0 "))+1,9999) if it doesn't work, then use this one, I replaced "," with ";" =SOLDAN(METNEÇEVİR(A1;"#,##0 ");BUL(",";METNEÇEVİR(A1;"#,##0 "))-1) & "." & PARÇAAL(METNEÇEVİR(A1;"#,##0 ");BUL(",";METNEÇEVİR(A1;"#,##0 "))+1;9999) tell me if it doesn't work....

excel,vba,excel-vba,excel-2010,excel-2007

Here is a somewhat different approach. I have set up a user defined class called cUUID. The class has the properties of the UUID, Response, ResponseCode and a Collection consisting of the paired Response and ResponseCode. We create a Collection of this class object, where each member of the collection...

Hey everyone I got my problem solved huuuuuh the problem was auto fill that increased the table array range due to that vlookup generated #N/A. When I drag the first cell the range started increasing and my array range was ,D1:D255, and after D93 as there was no information stored...

I beleive the limit for a single Excel column is actually 255 characters. I would test for longer values upstream in the SSIS flow and either split out those rows or truncate long data using a Derived Column.

I have figured it out. Here's the code below If method = "Spaced" Then Range("B29").Select Range("B29").Comment.Visible = False Range("B29").Comment.Text Text:="" & Chr(10) & "" Range("B29").Select ActiveCell.Comment.Visible = True Range("B29").Comment.Shape.Select True Selection.ShapeRange.Fill.Transparency = 0# Selection.ShapeRange.Line.Weight = 0.75 Selection.ShapeRange.Line.DashStyle = msoLineSolid Selection.ShapeRange.Line.Style = msoLineSingle Selection.ShapeRange.Line.Transparency = 0# Selection.ShapeRange.Line.Visible =...

excel-vba,if-statement,compiler-errors,excel-2007

It is because the With block: With Ws.Range("cGroup") 'where to look is never ended within the If block that it is placed in....

excel,excel-formula,excel-2010,excel-2007

I assume that means these are actually text formatted datetimes - you can test by trying to change the format, e.g. try to change to "Number" - if nothing changes theses are text values. You can still subtract, though, if the datetimes are in a valid format for your region....

How about: =NOW()-(WEEKDAY(NOW(),1)) This will return the time 'now' minus the number of days from Saturday where Sunday = 1, Monday = 2 etc. Note, this does include the timestamp along with the day (eg. 6/6/2015 14:48). If you want just the date you could daisy-chain a text function around...

excel,vba,excel-vba,excel-2007

Are you trying to access the entire columns as arguments in Sumifs? If so, try this: ws2.Range("B83") = Application.WorksheetFunction.SumIfs _ (ws3.ListObjects("SALES").ListColumns(26).DataBodyRange, _ ws3.ListObjects("SALES").ListColumns(4).DataBodyRange, _ ws2.Range("A82")) ...

excel,vba,excel-vba,excel-2010,excel-2007

You should have your functions actually return a value: Sub test() getthename = test2(test1("elephant")) End Sub Function test1(NewTitle As String) As String If NewTitle = "elephant" Then test1 = "horse" Else test1 = "pig" End If End Function Function test2(NewTitle As String) As String If NewTitle <> "horse" Then test2...

excel,excel-formula,excel-2007,formula,formulas

this should work: =INDEX(D1:D10,MATCH(NOW()-TODAY(),E1:E10,1)) NOW()-TODAY() gets the current date, MATCH find the row of the first value that is smaller or equal than the value and index returns the name in the corresponding row....

Answer: =IF(I4+45>EOMONTH(I4,0)+15,EOMONTH(I4,1)+15,EOMONTH(I4,0)+15)

sql-server-2012,64bit,excel-2007,import-from-excel,windows-server-2012-r2

Had to install this driver: https://www.microsoft.com/en-us/download/confirmation.aspx?id=13255 After that it works :)...

excel,vba,excel-vba,excel-2007,excel-2013

Since you want to avoid the clipboard and only copy Values, you can use assignment to the Value property instead of Range.Copy Something like this Sub Demo() Dim Sheet As Worksheet Dim FoundLocationSheet As Boolean Dim n As Long Dim rSource As Range Dim rDest As Range Dim AllSheet As...

It's probably working when you step through coincidentally because you happen to be on the sheet "Drawing" when running the macro. To access properties or parameters of an object, you need to use the "." Since you are already declaring what you are trying to access with the WITH statement....

excel,vba,excel-vba,excel-2007,excel-addins

It seems that Microsoft has written a KB Article about this as well: MS KB 832136. From the article: Cause: This problem may occur when one of the following conditions is true: In Excel 2007, the VBA array is longer than 8,203 characters in length. Workaround: Microsoft suggest that in...

Just click on the column and Format > Cells... ...

excel-vba,excel-2007,pivot-table

You can iterate through the PivotItems and check the Name against your test. Sub CheckIfPivotFieldContainsItem() Dim pt As PivotTable Set pt = Sheet1.PivotTables(1) Dim test_val As Variant test_val = "59" Dim pivot_item As PivotItem For Each pivot_item In pt.PivotFields("C").PivotItems If pivot_item.Name = test_val Then Debug.Print "MATCHES" End If Next pi...

sql-server,excel,ssis,excel-2007,sqlbulkcopy

Ok so I solved this by changing the method I was employing. Now everything works perfectly. I used a data adaptor instead as follows. I still don't know why previous code didn't work correctly OleDbDataAdapter adapter = new OleDbDataAdapter(exceldataquery, oledbconn); adapter.Fill(dataTable); SqlBulkCopy bulkcopy = new SqlBulkCopy(sqlconnectionstring); bulkcopy.BatchSize = 1000; bulkcopy.DestinationTableName...

excel,excel-vba,excel-formula,excel-2010,excel-2007

The following formula will do it (array formula which has to be entered using Ctrl + Shift + Enter): =INDEX($C$5:$C$9,MATCH(1,($B$5:$B$9=$E5)*($A$5:$A$9=F$4),0)) I assume that: your data of Table 1 is in A5:C10 E5 has the value of "Widget" F4 has the date value This array formula should be entered in F5....

Assuming you have sorted Sheet1 by subject. I will put some pieces together for you. This is untested as is, because it's not complete. However, these concepts will get you what you want to accomplish. I would start by creating a second sheet. You will need to keep track of...

The form property "WhatsThisButton" displays the question mark icon next to the close button, but this does nothing without creating an actual help file and assigning it to your form, this is not an easy thing to do. Far easier is to display text as you have described, each control...

vba,excel-vba,excel-2007,excel-2013,userform

As from the comments, I see you don't need anymore to know about points 1 and 2, I will hence limit my answer to the point 3. Last Part of this question if I have a Textbox in the userform that contains a date in the format 1/18/2015 is there...

The logical "AND" is not an operator in Excel, it is just another function. Excel doesn't know how to evaluate =FUNCA() AND FUNCB(). Instead, wrap the two functions inside the AND function: =AND(NOT(ISBLANK($A8)), ISBLANK(M8)) ...

excel,excel-vba,excel-formula,excel-2010,excel-2007

Alright, this is not very elegant, but the first thing that I came up with, that kinda works. use this formula to delete the last word in your bulk of text ("members") Assuming your text is in A1: =LEFT(A1,FIND("|",SUBSTITUTE(A1," ","|",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))-1) This formula gets you the last word of a...

excel,vba,excel-vba,excel-2010,excel-2007

Update: I haven't been able to reproduce this problem in a new workbook, so it may be workbook corruption. Normally Range.Interior.Color is reliable across versions, for all colors represented. FWIW, this workbook was sent to me by another person, and that person is on Excel for Macintosh while I am...

excel,excel-formula,excel-2007

If your records are located in the same cells in both worksheets, then you can use something like following: =INDIRECT($B$2&"!"&ADDRESS(ROW();COLUMN())) IF now, then you can easily offset them using given ADDRESS function P.S. Please be aware with INDIRECT function that it slows down calculation performance on your workbook (few functions...

excel,excel-vba,excel-2010,excel-2007

For different approach please see below: Sheet 1 layout (Source): Sheet 2 Layout (Target): Sub SalesRegion() Dim ws1, ws2 As Worksheet Dim wb As Workbook Dim ws1LastRow, ws2LastRow, salesVal As Long Dim destFind, dateFind As Range Dim destStr As String Dim dateStr As Date Dim targetCol, targetRow As Long Set...

excel,excel-formula,excel-2007

You can use Index and Match to select the column to sum. Something like this =SUMIFS(INDEX($B$4:$G$8,,MATCH(I1,$B$3:$G$3)),$B$4:$B$8,">="&I2,$B$4:$B$8,"<="&I3) You can also take advantage of the Table Structured Addressing like this (tested in Excel 2010, so Table formats may be slighly different in Excel 2007) =SUMIFS(INDEX(MyTable,,MATCH(I1,MyTable[#Headers])),MyTable[Date],">="&I2,MyTable[Date],"<="&I3) ...

excel,excel-formula,excel-2010,excel-2007

If its just as simple as removing everything after, including, the ? then this will do it: =LEFT(A:A,FIND("?", A1)-1) Edit: If you want to catch the #VALUE! error when there is no ? simply use IFERROR: =IFERROR(LEFT(A:A,FIND("?", A1)-1), A1) Example rows: www.mysite.come/api/Customer?id=12333&name=jack www.mysite.come/api/Department?id=52365&name=COP www.mysite.come/api/Customer?id=13333&name=mathew Output: www.mysite.come/api/Customer www.mysite.come/api/Department www.mysite.come/api/Customer ...

excel,excel-vba,excel-formula,excel-2007

you can use this formula of index and match: =IFERROR(INDEX($A:$C,MATCH(1,($A:$A=$G3)*($B:$B=H$2),0),3),"") paste it in the first cell of your table H3, and drag and fill to the right and then select the entire row and fill down till end. it should work. if error(();"") : you will get empty cells if...

vb.net,visual-studio,excel-2007,ribbon,menubar

The tab-scaling feature was introduced with Office 2010. So, you need to use Accessibility API to get the required information (do whatever you need).

The data was imported as a ListObject table and held a header that was hidden with .ListObjects.ShowHeaders = False. To fix this, the header row must be exposed and then the worksheet's row 1 transferred into the table's header row. Here is some quick code that repairs the data imported...

excel,vba,excel-vba,excel-2007,excel-2013

I've managed to find the correct answer, it's actually rather simple. All you have to do is add conditional formatting and then set the .Interior.Color to the same as what the .DisplayFormat.Interior.Color is and then delete the conditional formatting. This will do exactly what is requested in the main post;...

c#,excel-2007,compatibility,excel-2003,excel-interop

Found a solution! within vs2013, they offer excel 2007 addin, within that predefined interoperability, there is a way to create a file from a template, the part that was throwing me off was the part of the parameters which shows: "Type.missing". an example of this code structure would be as...

excel,excel-formula,excel-2010,excel-2007

For current run, assuming data in C2:Z2, try this array formula: =IFERROR(MATCH(TRUE,C2:Z2>24,0)-1,COUNT(C2:Z2)) Confirm with CTRL+SHIFT+ENTER For longest streak try this version based on the cell references used in your comment =MAX(FREQUENCY(IF(P7:BB7<=24,COLUMN(P7:BB7)),IF(P7:BB7>24,COLUMN(P7:BB7)))) Again confirm with CTRL+SHIFT+ENTER or to allow blanks in the range (which would end a streak) you can use...

excel,excel-formula,excel-2007,formula

We can do this with a helper column: Helper column, keep the name if the count of 5 equals to 5. Enter below to K2 and fill down. =IF(COUNTIF(E2:J2,5)=5,D2,"-") Then we are counting how many times name appears in the helper column. Enter below to B2 and fill down. =COUNTIF($K$2:$K$11,A2)...

excel,excel-formula,excel-2010,excel-2007

You can leave C1 as a date and the data as it is - just change your formula to the following =SUMIFS(A:A,B:B,">="&C1,B:B,"<"&C1+1) Excel stores dates as integers, times are simply fractions of the day so every C1 date (no matter what time) falls somewhere between C1 and C1+1. This formula...

excel,excel-2007,aspose,aspose-cells

It seems you need to set the ExportCellName name property to true before saving to xlsx/xlsm format. Please see the following sample. //Create your workbook Workbook workbook = new Workbook(filePath); //Do your processing //Save your workbook with export cell as true OoxmlSaveOptions opts = new OoxmlSaveOptions(); opts.ExportCellName = true; workbook.Save("output.xlsx",...

excel,excel-formula,excel-2007

=COUNTIF(Master!B:B,"*Buildings*")-COUNTIFS(Master!F:F,"90",Master!B:B, "*Buildings*") ...

c#,asp.net,sql-server-2008,excel-2007

I've found the answer to my own question. I changed the way to retrieve data from an excel file, instead of using OLEDB changed to use Office Interop. So I added a reference Office Interop and modify the source code to be like this: protected void btnUpload_Click(object sender, EventArgs e)...

You can probably do something like: # if those are the only files in the folder, you don't even need a pattern filelist <- list.files(pattern = "^percent.*\\.csv$") # read all file names according to pattern files <- lapply(filelist, read.csv, header=TRUE) # read all files in filelist files <- lapply(files, function(x)...

r,loops,merge,excel-2007,lapply

Try library(tools) source <- file_path_sans_ext(filelist) files1 <- Map(cbind, files, Source=source) files1 #[[1]] # Col1 Col2 Source #1 A 0.5365853 Filname1 #2 A 0.4196231 Filname1 #[[2]] # Col1 Col2 Source #1 A 0.847460 Filname2 #2 C 0.266022 Filname2 #[[3]] # Col1 Col2 Source #1 C -0.4664951 Filname3 #2 C -0.8483700 Filname3...

excel,vba,excel-vba,excel-2010,excel-2007

Your example code defines the formatting to be carried across as "text, general, time, date... etc". While the number formatting (a subset of the Properties of a cell) can easily be accommodated, delving further into enumerating the vast number of properties and subproperties of a Cells object is counter-productive when...

excel,excel-formula,excel-2007

Go to the tab "Formulas" and click on the little triangle next to "Calculation options". There select "Automatic".

Assuming that the functions you refer to (but did not provide the code to support) will return Range type object (or Nothing), then you can simply use the Offset method: If Not rngRatio Is Nothing Then With rngRatio Debug.Print .Offset(0,1).Address, .Offset(0,1).Value '$C$57 Debug.Print .Offset(0,2).Address, .Offset(0,2).Value '$D$57 End With End If...

Remove the space in the Specification Value. The First() function takes one expression or argument. You are passing it two, so it thinks you are passing an expression with two arguments and have forgotten the operator.

excel,excel-formula,excel-2007,highlighting,cells

For this fixed range, put the following code into the ThisWorkbook code module in the SheetSelectionChange event. It should then operate on any sheet in the workbook. Should get you going to consider generalising it. Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range) If Not Intersect(Target, Range("E3, E6,...

excel,vba,excel-vba,excel-2010,excel-2007

There is a property .LockAspectRatio = msoTruesetted per default for shapes which contains images. This causes your described behavior. To avoid this you could switch this to msoFalse, then change .Height and .Width and the switch it to msoTrue again: Sub picture_size() ActiveSheet.Shapes.Range(Array("Picture 2")).LockAspectRatio = msoFalse ActiveSheet.Shapes.Range(Array("Picture 2")).Width = ActiveSheet.Shapes.Range(Array("Picture...

excel,excel-formula,excel-2007,vlookup

You can try this provided that each sheet have unique ID's only (no duplicate). Kinda long formula though and not Vlookup. =IFERROR(INDEX(Sheet1!$A:$E,MATCH($A2,Sheet1!$A:$A,0),MATCH(B$1,Sheet1!$1:$1,0)), IFERROR(INDEX(Sheet2!$A:$E,MATCH($A2,Sheet2!$A:$A,0),MATCH(B$1,Sheet2!$1:$1,0)), IFERROR(INDEX(Sheet3!$A:$E,MATCH($A2,Sheet3!$A:$A,0),MATCH(B$1,Sheet3!$1:$1,0)),""))) Basically it is a simple Index-Match combination with nested Iferror to try searching on different sheets. So if you have your sheets set up like below:...

You can use the generic Select Case True. It will evaluate each Case and do the actions in the first true one: Select Case True Case Me.OptionButton1.Value = True ' actions ... Case Me.OptionButton2.Value = True ' actions ... End Select ...

excel,excel-2007,conditional-formatting

Assuming a table like this | Pd Date | Status | |----------|---------| | 4/1/2015 | paid | | 5/7/2015 | denied | | 5/11/2015| paid | | 5/11/2015| pending | You should be able to set conditional formatting using a formula to the row with =AND(ISNUMBER(SEARCH("paid",$B2)),MONTH(TODAY())=MONTH($A2)) Adding a year criteria...

excel,date,if-statement,excel-2007

To extract fiscal year use: =YEAR(A1) + IF(MONTH(A1)>=4,1,0) I think in your case you would need: =YEAR(A1) - IF(MONTH(A1)>=4,0,1) If the months is before 4th month then subtract 1 year, else keep the same year. I wouldn't convert it to a full date DD/MM/YYYY with a 1 year subtracted, to...

Your code have a lot of hanging statements (For Loop and If Statements). I don't know what you mean by load but if what you want is to copy Worksheets from WorkbookA that is not in the list (table in sheet in WorkbookB), you can try below: Code in WorksheetB:...

This custom format code works for the examples in your question: [>=100]#\,#0;# ...

This is the solution I used: =DATE(YEAR(NOW()),MONTH(NOW()),DAY(NOW()-(WEEKDAY(NOW(),1))))

I like NPOI, but sometimes I get myself in trouble with some weird bugs. Your issue seems one of that kind. To solve this one, you have to: Create a row in your Excel template and format it as you need. Create a new row (where you want to write)...

excel,if-statement,excel-formula,excel-2007

With a changed lookup array in Sheet2: I suggest: =ROUND(C2*IF(B2="PKG",INDEX(Sheet2!C$2:C$7,MATCH(C2,Sheet2!B$2:B$7,1)),IF(B2="POPULAR",INDEX(Sheet2!C$8:C$11,MATCH(C2,Sheet2!B$8:B$11,1)),INDEX(Sheet2!C$12:C$14,MATCH(C2,Sheet2!B$12:B$14,1)))),2) in D2 copied down to suit....

If you want to create nicely formatted Outlook emails then you need to generate emails with formatting. Pure text-based-emails are evidently not sufficient and hence you must be looking for HTML formatted emails. If that's the case you probably aim to dynamically create HTML code with your VBA to mimic...

excel,excel-formula,excel-2010,excel-2007

If you prefer to use a non-random prefix that just contains the next letter, you could do something like this starting in C2:- =IF(B2=B1,CHAR(96+COUNTIF(B$1:B1,B2))&"."&B1,B2) I'm assuming that the names are sorted, have a heading and are in column B. If you didn't want to sort them, this would still work...

excel,vba,excel-vba,excel-2007

you can try this at the top: on error resume next ActiveWorkbook.Sheets("your sheet").delete on error goto 0 if it exists, it will be deleted and your code will make the new one. Does it help?...

excel,vba,variables,excel-2007

You can create your own custom Dictionary or Collection in VBA for Excel 2007. Then you can "name" your variables, and use another string variable to indirectly access those "named variables". Choice of using Dictionary or Collection is how easy you need it to change the value of a "named...

excel-formula,excel-2007,formula

Aha! This is actually quite simple and straightforward after reading Excel SUMPRODUCT Function. Thanks to the author Dave Bruns! The formula is =SUMPRODUCT(--(C1:C4<B1:B4),--(ISNUMBER(C1:C4)),C1:C4) ...

You could achieve that by using this in D1: =INDEX(A2:A6,MATCH(SMALL(B2:B6,2),B2:B6,0)) SMALL will allow you to find the second oldest value MATCH will help you find its index in the array INDEX will find this index value in the first column...

This macro snippet should reorganize your rows of data according to your sample images. Dim rw As Long With ActiveSheet For rw = 2 To .Cells(Rows.Count, 1).End(xlUp).Row .Cells(rw, 1).Resize(1, .Cells(rw, Columns.Count).End(xlToLeft).Column).Copy _ Destination:=.Cells(1, Columns.Count).End(xlToLeft).Offset(0, 1) .Rows(rw).Clear Next rw End With I've removed the original data from rows 2 through 7....

Is pasting all names on one sheet an option? If so, paste all the names from 12 sheets into the same column on separate sheet, once done, just create a pivot table from it adding names as column labels, this will give you a list of all unique names across...

This code will avoid writing to cells one at a time and uses an array to greatly speed up processing time: Sub tgr() Dim wsInput As Worksheet Dim wsOutput As Worksheet Dim ACell As Range Dim arrResults() As Variant Dim ResultIndex As Long Dim sCurrent As String Dim sLine As...

excel,vba,excel-vba,excel-2007

The comments made by @Paradox and @Tim are all valid. To specifically answer your question, you cannot change the ActiveCell from code but instead use the Range or Cells to set a reference to the range: Public Sub PriceSearch(SaSh As Worksheet) Dim StartNumber As Integer Dim EndNumber As Integer Dim...

=HYPERLINK("#Main!C"&MATCH((RIGHT(CELL("filename";A1);LEN(CELL("filename";A1))-FIND("]";CELL("filename";A1);1)))*1;Main!C:C;0);RIGHT(CELL("filename";A1);LEN(CELL("filename";A1))-FIND("]";CELL("filename";A1);1))) Ok, couple days fiddling and I came up with the solution myself. The above string works. Looks like the issue was with formatting as when the tab name is multiplied by 1 (thus making it a number) everything starts working. Go figure.....

excel,excel-formula,excel-2007

Assuming you start with filtered data already, you need to have the header cell selected. From there: ALT+DOWN, F, A. If the filter is not already created then throw in a CTRL+SHIFT+L at at the start. If you are not in the header row, some combination of CTRL+ARROW keys can...

As soon as I hit post I figured out the problem. Instead of using the .Formula property of an Excel.Range, you have to use the .FormulaArray property instead. xlWorkSheet.Range("AF" & intCurrentRow.ToString & ":AG" & intCurrentRow.ToString).FormulaArray = formulas ...

excel,vba,excel-vba,excel-2007,data-import

Assuming you don't have any other information stored on those sheets besides the querytable, try this (I cut out your with statement for space): Sub ImportManyTXTs() Dim strFile As String Dim Sht As Worksheet Dim ws As Worksheet strFile = Dir("C:\location\of\folder\with\textfiles\*.txt") Do While strFile <> vbNullString strFile2 = Replace(strFile, ".txt",...

excel,ms-access,vbscript,ms-access-2007,excel-2007

You can round only to zero. So you need to transform the number in such a way that 0.07 is 0.00. For example you can add 0.03 or subtract 0.07. Then after rounding you do the opposite (subtract 0.03 or add 0.07) and get the number you want. The code:...

This was already answered in this external discussion: https://phpexcel.codeplex.com/discussions/286662 Worksheet->duplicateStyle() does indeed not work across files. But there is a workaround: temporarily add the source sheet into the target document as a new sheet using addExternalSheet(), copy formattings over, then remove the temporary sheet again. I still did not found...

For some types of Hyperlinks overwriting the cell value deletes the hyperlink (eg links to locations in the workbook), for other like yours it does not. So you need to actively delete the hyperlink. Also, you can iterate the Hyperlinks collection of a range or Worksheet, much more efficient and...

excel,excel-vba,excel-formula,excel-2007

In an unused cell to the right use this formula, =OFFSET($A$1, INT((ROW(1:1)-1)/2), COLUMN(A:A)-1+MOD((ROW(1:1)-1), 2)*2) Fill right one column and fill down as far as necessary. Your results should resemble the following. ...