First we will dim the variable Dim Rws As Long, Rng As Range, a As Range Dim Rws2 As Long, rng2 As Range, c As Range Dim sh As Worksheet, ws As Worksheet We need to set the worksheets before we set the ranges Set sh = Sheets(“Sheet1”) Set ws = Sheets(“Sheet2”) Set the range … Read more
This is not so straightforward a problem because of having to consolidate the information by date. You also do not indicate what you want to happen should there be more than one identical code associated with a particular date. I chose to ignore it, and only list the unique codes, but you can modify the … Read more
This should do the trick. If not, it will at least get you started. Sub Expand_Occurance() Dim ItemCounter As Long, shBottom As Long, NewItemRow As Long, OccuranceCounter As Long Dim sh As Worksheet Set sh = ActiveSheet shBottom = sh.Cells(Rows.Count, 1).End(xlUp).Row ‘get the bottom row of column 1 NewItemRow = shBottom + 1 ‘and the … Read more
Look for a word of column 1 and then search that word in another column of another excel sheet and use the adjacent information to fill the cell solved Look for a word of column 1 and then search that word in another column of another excel sheet and use the adjacent information to fill … Read more
Here is a sample that will iterate over a range of cells (B1:B100) which you can modify, and uses the filename from the cell one column to the left (so, from Column A), and sizes the image to fit within cell in column B. Sub InsertPic() Dim pic As String ‘file path of pic Dim … Read more
try to use this code: First way (you can use user defined function): Function getSomeData(E3 As Range, Table5 As Range, F26 As Range) getSomeData = “” If WorksheetFunction.VLookup(E3, Table5, 2, 0) >= F26 Then getSomeData= WorksheetFunction.VLookup(E3, Table5, 4, 0) * F26 End If End Function Than you can call it in any cell just typing … Read more
Public Sub DataRefresh() DisplayAlerts = False For Each objConnection In ThisWorkbook.Connections ‘Get current background-refresh value bBackground = objConnection.OLEDBConnection.BackgroundQuery ‘Temporarily disable background-refresh objConnection.OLEDBConnection.BackgroundQuery = False ‘Refresh this connection objConnection.Refresh ‘Set background-refresh value back to original value objConnection.OLEDBConnection.BackgroundQuery = bBackground Next Workbooks(“DA List.xlsm”).Model.Refresh DoEvents For i = 1 To 100000 Worksheets(“DA List”).Range(“G1”) = i Next i DoEvents … Read more
First up, bring your CF conditions into a new column, so that you can directly access the resulting TRUE/FALSE results by either Formulas or VBA. Depending on what version of Excel you have, you have a number of options to accomplish what you’re trying to do here, including: Pull all the data into VBA using … Read more
This is the method I described in my comment above: I would probably look at iteration over the rows, building a delimited string (comma or tab delimited) to represent each “row” in the formatted output, based on the ID, and then write that out to a TXT file that could easily be read by SPSS … Read more
go back to the beginning insert a document variable in a new word document using following sequence (word 2016) insert tab … text … quick parts … field … categories: document automation … field names: docVariable … put in variable name xxxx then run this code Sub aaa() ‘ActiveWindow.View.ShowFieldCodes = Not ActiveWindow.View.ShowFieldCodes ‘ toggle field … Read more
Put this into your Commandbutton_Click event handler: Dim strValueToPrint as String, strOutput as String Dim intRepeatCount as Integer, i as Integer strValueToPrint = “1” ‘Change this to be the value that you want to repeat for i = 1 to intRepeatCount strOutput = strOutput & strValueToPrint & “,” Next Activecell.Value = strOutput 5 solved Print … Read more
You need to use “PtrSafe” when declaring function : Private Declare PtrSafe Function Also in 64bit Excel, use LongLong instead of Long 1 solved Converting Excel VBA 32bit code to 64bit
I was curious so I did one. Sub NDArray() Dim arr() As Double a = InputBox(“Number of numbers”) ReDim arr(a – 1) As Double With Application.WorksheetFunction ‘fill the array with random numbers that fit a normal dist For i = 0 To a – 1 ‘”A/100″ is the target mean and “A/200” is target Std. … Read more
All worksheets in a Workbook got an Index property. Index 1 means first sheet, index 2 second one, and so on. So the last sheet will have an Index equal to Sheets.Count. Knowing this, try replacing this part: For Each fnameCurFile In fnameList countFiles = countFiles + 1 Set wbkSrcBook = Workbooks.Open(Filename:=fnameCurFile) For Each wksCurSheet … Read more
it’d be something like follows: Sub MyMacro() With Selection Sheets.Add.name = .Cells(1, 1) .Offset(1).Resize(.Rows.Count – 1).Copy Sheets(.Cells(1, 1).value).Range(“A1”) End With End Sub feel free to add all necessary checks for assure error handling (e.g.: no selection made, only 1 row selected, target sheet already existent, …) of which you may find dozens of examples around … Read more