Posts by dbrown14
Please note that on 14th December 2023 users will experience an expected outage whilst we make upgrades to our network. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.
-
-
-
Re: Offset Match Mod Row formula to select data within spreadsheet
Is this what you're looking for?
=INDIRECT(ADDRESS(AGGREGATE(15,6,ROW(O5:O30)/((MOD(ROW(O5:O30)-SMALL(ROW(O5:O30),1),3)=0)*(O5:O30="X")),1),15-10)) -
Re: Find value from open workbook in closed workbook and copy adjacent cells
You might want to open you CSV file to see if the formats stay there.
Try this though.Code
Display MoreOption Explicit Sub macro() Dim ex As Excel.Application Dim wrkbk As Workbook Dim sht As Worksheet Dim row As Integer Dim filePath As String Dim a As Variant 'Path to closed file filePath = "C:\Users\user\Documents\Today.csv" 'Stop updating the screen Application.ScreenUpdating = False 'Insert new row Rows(1).Insert 'Copy a = Range("A2:N2").Value 'Paste Range("A1:N1").Value = a 'Create new instance of excel Set ex = New Excel.Application 'Open the workbook Set wrkbk = ex.Workbooks.Open(filePath, , True) 'Grab the first sheet in wrkbk Set sht = wrkbk.Sheets(1) 'Find value in B1 in colum A of closed workbook row = sht.Range("A:A").Find(Range("B1").Value, , xlValues, xlWhole).row 'Copy a = sht.Range("F" & row, "K" & row).Value 'Paste Range("C1").Resize(, 6).Value = a 'Close workbook wrkbk.Close 'Exit excel ex.Quit Range("C:C").NumberFormat = "dd/mm/yyyy" 'Set the screen to update Application.ScreenUpdating = True End Sub
-
Re: Trying to sum values in one worksheet based on conditions in another worksheet
Wrap you dates ("10/31/2011","10/1/11") with the DATEVALUE function.
=DATEVALUE("10/31/2011")
This will convert you dates to numbers fixing your problems -
Re: Update sheet 2 information
Try this
-
Re: Find value from open workbook in closed workbook and copy adjacent cells
Try this
Code
Display MoreOption Explicit Sub macro() Dim ex As Excel.Application Dim wrkbk As Workbook Dim sht As Worksheet Dim row As Integer Dim filePath As String Dim a As Variant 'Path to closed file filePath = "C:\Users\user\Documents\Today.csv" 'Stop updating the screen Application.ScreenUpdating = False 'Insert new row Rows(1).Insert 'Copy a = Range("A2:N2").Value 'Paste Range("A1:N1").Value = a 'Create new instance of excel Set ex = New Excel.Application 'Open the workbook Set wrkbk = ex.Workbooks.Open(filePath) 'Grab the first sheet in wrkbk Set sht = wrkbk.Sheets(1) 'Find value in B1 in colum A of closed workbook row = sht.Range("A:A").Find(Range("B1").Value, , xlValues, xlWhole).row 'Copy a = sht.Range("F" & row, "K" & row).Value 'Paste Range("C1").Resize(, 6).Value = a sht.Range("F:F").NumberFormat = "dd/mm/yyyy" 'Close workbook wrkbk.Close 'Exit excel ex.Quit Range("C:C").NumberFormat = "dd/mm/yyyy" 'Set the screen to update Application.ScreenUpdating = True End Sub
-
Re: Find value from open workbook in closed workbook and copy adjacent cells
Which cells are date formatted that are being changed?
-
Re: Have excel select the right range then choose a number from it based on a conditi
I try to explain the best I can in this workbook
-
Re: Find value from open workbook in closed workbook and copy adjacent cells
Try setting the format of the cells to the correct format again, then run the macro. If my macro changes the formatting of the cells again contact me here again.
-
Re: To duplicate Template worksheet and create link for worksheet in the Summary Page
Can you explain 2 a little more?
Try these things -
Re: VBA Copy paste values to another sheet
Try this
Code
Display MoreOption Explicit Sub macro() Dim date1 As Date Dim a As Variant Dim start As Long Dim i, ii As Long With CreateObject("Scripting.Dictionary") .CompareMode = vbTextCompare date1 = "4/1/2012" a = Sheets("Sheet1").Range("B2", Sheets("Sheet1").Range("B2").End(xlDown).Offset(0, 3)).Value For i = 1 To UBound(a, 1) If Not .exists(a(i, 1)) Then start = Sheets(a(i, 1)).Range("B:B").Find(date1, , xlFormulas, xlWhole).Row + 1 .Add a(i, 1), start End If start = .Item(a(i, 1)) For ii = 1 To UBound(a, 2) Sheets(a(i, 1)).Cells(start, ii + 1).Value = a(i, ii) Next .Remove a(i, 1) .Add a(i, 1), start + 1 Next End With End Sub
-
Re: Have excel select the right range then choose a number from it based on a conditi
Try this
=INDEX(B2:D4,MATCH(G7,A2:A4,0),MATCH(INDEX(A7:B22,MATCH(F7,A7:A22,0),2),B1:D1,0)) -
Re: Find X in a row of closed workbook assign the column letter to a variable
Try this.
Code
Display MoreOption Explicit Sub macro() Dim ex As Excel.Application Dim wrkbk As Workbook Dim sht As Worksheet Dim rngX As Range Set ex = New Excel.Application Set wrkbk = ex.Workbooks.Open("C:\workbook\closed.xlsx") Set sht = wrkbk.Sheets("pivot") Set rngX = sht.Range("A4:D4").Find("X", lookat:=xlPart) If Not rngX Is Nothing Then MsgBox "Found at " & rngX.Address End If wrkbk.Close ex.Quit End Sub
-
Re: Copy Rows Between Worksheets Based On Single-Column Criteria
Import this file into your VBA Editor.
With VBA Editor open:
File->Import File...->filePath -
Re: Import Data to .xls
This is very vague. Try uploading an example workbook and rpt file.
-
Re: Copy data from a file in netwrok based on File Name & Folder name key in by user.
I see what your problem is now.
Try this.The code you had before made everything inside the quotes a string. Therefore, "Range("C4").Value" was literally place in the string open_path.
The code I provide, starts and end with a string added onto the evaluation of "Range("C4").Value"
To see the difference you can use this. -
Re: Validation Across Several Worksheets
Try something along these lines.
Try uploading an example workbook for better helpCode
Display MoreDim sht As Worksheet Dim name As String Dim rng, rng2 As Range Dim allow As Boolean allow = False For Each sht In Sheets If sht.name Like "*Goals*" Then Set rng = sht.Range("C:C,X:X").Find(name, , xlValues, xlWhole) Set rng2 = sht.Range("AC:AC").Find(name, , xlValues, xlWhole) If rng Is Nothing Then allow = True Exit For ElseIf rng2 = Nothing Then Exit For End If End If Next If Not allow Then MsgBox "Employee Not Available" End If
-
-
Re: Trying to sum values in one worksheet based on conditions in another worksheet
Here's the dynamic form.
=SUMPRODUCT(INDIRECT(CONCATENATE("Travel!M7:M",LARGE(ROW(Travel!M:M)*(Travel!M:M<>""),1)))*((INDIRECT(CONCATENATE("Travel!J7:J",LARGE(ROW(Travel!M:M)*(Travel!M:M<>""),1)))="October")*(INDIRECT(CONCATENATE("Travel!G7:G",LARGE(ROW(Travel!M:M)*(Travel!M:M<>""),1)))="Domestic")))