Split: Now you have obtained the JSON string you can parse with Split function. Here I am reading the JSON in the comments from a cell Option Explicit Public Sub GetExchangeRate() Dim json As String json = [A1] Debug.Print Split(Split(json, “””5. Exchange Rate””: “)(1), “,”)(0) End Sub JSON Parser: Here you can use a JSON … Read more
Would it work this way? Sheets(“Weekly Plan”).Select ActiveSheet.Unprotect Sheets(“Template”).Select Rows(“1:21”).Select Selection.Copy Rows(“6:6”).Select Range(“B6”).Activate Selection.Insert Shift:=xlDown Sheets(“Weekly Plan”).Select Range(“K10”).Select Range(“K28:K47”).Select Range(“K47”).Activate Application.CutCopyMode = False Selection.Copy Range(“K7”).Select ActiveSheet.Paste Application.CutCopyMode = False ActiveSheet.Protect solved Why does my macro only work once?
Try this code (comments in code): Sub Expand() Dim currentRow As Long, lastRow As Long, table As Variant, i As Long, _ valuesInOneRowCol1 As Variant, valuesInOneRowCol2 As Variant, valuesInOneRowCol3 As Variant lastRow = Cells(Rows.Count, 1).End(xlUp).Row currentRow = 2 ‘read hwole range to memory and clear the range to fill it with expanded data table = … Read more
If you want to avoid the clipboard may I suggest R1C1 formula format: Sub fillsheet() Dim ows As Worksheet Dim tws As Worksheet Dim rng As Range Set ows = Worksheets(“Sheet1”) Set tws = Worksheets(“Sheet2”) Set rng = ows.UsedRange tws.Range(rng.Address()).FormulaR1C1 = “='” & ows.Name & “‘!RC” End Sub 1 solved Macro in Excel to Copy … Read more
You can search a range for a value using Range.Find Range.Find returns Nothing if no match is found or a Range if a match is found. You can check if two objects refer to the same object using the is operator. Here is an example: Sub lookup() Dim TotalRows As Long Dim rng As Range … Read more
Turn your template into an Excel Table using the Ctrl + T keyboard shortcut. These have calculated columns that autofill formulas, and the tables themselves auto expand to accommodate new data. 0 solved How can I create a template that will auto populate/calculate cells based on a SKU value? [closed]
I solved the problem by letting VBA excecute Workbook_Open event ASAP. I put the whole earlier content of Private Sub Workbook_Open under “scraper” procedure and now it looks like this: Private Sub Workbook_Open Application.OnTime Now + TimeValue(“00:00:01”), “scraper” End Sub It must be that Excel won’t (sometimes?) open the file before executing Workbook_Open event. solved … Read more
This particular web page isn’t using <select> and <option>. That suggests to me that they are using some custom JavaScript to simulate a drop-down list using the illustrated <div> and <span> elements. In addition, they are using onselect rather than onclick to trigger event handlers. I can’t replicate your test case. However, I did make … Read more
With you setup you would use INDEX/MATCH with your FIND as an array formuls: =INDEX($D$1:$D$3,MATCH(TRUE,ISNUMBER(FIND($C$1:$C$3,A1)),0)) being an array formula it needs to be confirmed with Ctrl-Shift-Enter instead of Enter when exiting edit mode. 1 solved Excel substitute for FIND Function when searching for multiple criteria
No code needed – as @Nathan_Sav said a – a quick search will find what you’re after (I find “reference main form from subform” gives the best page at the top – http://access.mvps.org/access/forms/frm0031.htm ) SELECT * FROM Employees WHERE [First Name]=Forms![Form1]![txtFirstName] AND [Last Name]=Forms![Form1]![txtLastName] txtFirstName & txtLastName are the names I gave to the text … Read more
Your question is not “How to hide rows that have 0”, your code for that works already. Your question title should be How to find ActiveRange Asking the proper questions helps you find better solutions, quicker. Dim ws as WorkSheet: Set ws = Sheets(“Sheet1”) Dim lr as Long lr = ws.Cells(Rows.Count, “E”).End(xlUp).Row For each cell … Read more
Function findimage(Path As String, ImageList As String) Dim results Dim x As Long Dim dc ‘double comma Dim extension As String results = Split(ImageList, “,”) If Not Right(Path, 1) = “\” Then Path = Path & “\” For x = 0 To UBound(results) If Len(Dir(Path & results(x))) > 0 Then results(x) = True Else extension … Read more
For the solution i assume, that you have a list of file pathes to the files you want to read. To achive your desired result i propose to pass an input stream of each file to a String variable and store them in an Array, with length of number of input files. In the end … Read more
If you want your result to be in Sheet2, then this code will do what you expect, it will check the number of Columns on Sheet1 and copy all of them into Sheet2: Sub foo() Dim LastRow As Long Dim LastCol As Long Dim ws As Worksheet: Set ws = Sheets(“Sheet1”) ‘change this to the … Read more
The code considers a valid range to be from row 1 to the lowest, non-empty, cell in the column. Fair warning it is currently untested, so try it on a dummy version with known results first. You have to change the constant TargetPercent to match whatever X% you want. This could also be pretty slow … Read more