Excel vba loop throuhg all sheets containing first letter M
i've been trying to loop through a certain sheet number , i have 20 sheets that start all with M , like M1 , M2 and so on till M20 , my problem is that i'm looping and writing , but instead of writing only on the M sheets , i'm writing in all sheets.
Sub CountWSNames() Dim I As Long Dim xCount As Integer For I = 1 To ActiveWorkbook.Sheets.Count If Mid(Sheets(I).Name, 1, 1) = "M" Then xCount = xCount + 1 ThisWorkbook.Worksheets(I).Range("A50") = "V" 'This line must write only to M sheets Next MsgBox "There are " & CStr(xCount) & " sheets that start with 'M'", vbOKOnly, "KuTools for Excel" End Sub
Small piece of code similar to mine.
ThisWorkbook.Worksheets(I).Range("A50") = "V"
This line must only affect the Cell ("A50") on sheets starting with M.
You can use for each.
For each sht in ActiveWorkbook.Sheets If Mid(sht.Name, 1, 1) = "M" Then xCount = xCount +1 sht.Range("A50") = "V" 'This line must write only to M sheets End if Next
You need to wrap both syntaxes inside the
If you don't care about the sheet count, but just want to write a value to those sheets, then you can use:
Sub WriteToMSheets() Dim ws as Worksheet For Each ws in Thisworkbook.Worksheets If Ucase(Left(ws.Name,1)) = "M" Then ws.Range("A50").Value = "V" Next ws End Sub
See also questions close to this topic
Type Mismatch on Range Autofill
I have a code that Autofills Range("F2") but it doesn't work. It returns Type Mismatch.
Here is my code:
Range("F2").Formula = "=IsEmailValid(A2)" Range("F2").AutoFill Destination = Range("F2:" & "F" & Range("A" & Rows.Count).End(xlUp).Row)
This line of code always Return Type Mismatch
Range("F2").AutoFill Destination = Range("F2:" & "F" & Range("A" & Rows.Count).End(xlUp).Row)
424 runtime error of Excel VBA of below code
When I run the below code in Excel, I show 424 runtime error. But I can not find the reason of the problem.
Sub A2() [I1] = Worksheetfuction.Average(Range("C1", "G1")) End Sub
Hangman game runs okay in stepping mode but delays making shapes visible in normal mode
I am trying to set up this hangman game however, so far it only works in stepping mode.
What happens after pressing play is that if I choose a wrong letter, it does not show "hangman" shape until I make a correct guess.
So, for example, if I make 2 incorrect guesses (so there should be 2 shapes showing before repeating the loop), nothing happens until my 3rd guess is correct, then it shows both shapes at once.
The code works fine in stepping mode, if i go through the code with F8, there are no issues, shapes show up in order. Not really sure what could be the issue here.
Option Explicit Dim wks As Worksheet Dim word As Range Dim rect As Shape Sub Hangman() Dim fletter As Variant Dim myLetter As String Dim i As Byte Set wks = ThisWorkbook.ActiveSheet Set word = wks.Range("B1:J1") i = 1 Do Until wks.Range("b1:j1").Font.Color = VBA.ColorConstants.vbBlack Or i = 9 myLetter = VBA.InputBox("What is the next letter") Set word = Range("B1:J1").Find(myLetter) If Not word Is Nothing Then word.Font.Color = VBA.ColorConstants.vbBlack fletter = word.Address Do Set word = Range("B1:J1").FindNext(word) word.Font.Color = VBA.ColorConstants.vbBlack If word.Address = fletter Then Exit Do Loop Else Set rect = ThisWorkbook.ActiveSheet.shapes("Shape" & i) rect.Visible = msoTrue i = i + 1 End If Loop End Sub
Is there a way to suppress a prompt asking for query parameters when updating a data source (SAP) from an excel macro?
I am updating a data source from an excel macro by running
Application.Run("SAPExecuteCommand", "RefreshData", "DS_1")
, which generates a prompt asking for the user to input query parameters. However, I just want to go with the already set parameters. Is there a way to suppress the prompt altogether or perhaps effectively just click OK when prompted? (perhaps finding the element that is the OK button and clicking it from the same macro, without resorting to absolute coordinates?)
How To Identify Compile Error With Split Value
I have got through every compile error post on the site and I know it has something to do with the
.Value. I have tried setting it to
= Nowand adding
(Now, term.Value)similar to what others have done. Does anyone see why I'm getting the "Compile error: Method or data member not found"
Please let me know if you see anything wrong specifically with the
words() = Split(term.Value)code:
Sub FindSimilar() Dim phrases As Range, phrase As Range Dim terms As Range, term As Range Dim matches As String Dim words() As String 'ensure this has the correct sheet names for your workbook Set phrases = ThisWorkbook.Worksheets("Export").Range("B2:B4345") Set terms = ThisWorkbook.Worksheets("Topics").Range("D1847:D4847") For Each term In terms matches = "" words() = Split(term.Value) For i = 0 To UBound(words, 1) If Len(words(i)) > 2 Then Select Case words(i) Case "business", "example", "string" Case Else For Each phrase In phrases If InStr(1, phrase.Value, words(i)) Then matches = matches & phrase & "/" End If Next phrase End Select End If Next i If matches <> vbNullString Then term.Offset(0, 6).Value = Left(matches, Len(matches) - 1) Else term.Offset(0, 6).Value = "No match" End If Next term End Sub
Also, as a note, this code was working well and it just stopped working so I'm assuming it's just an update or some small text. I'm using Excel for Mac.
If statement for named range
I have a workbook with 2 sheets. One is Data and other Location
In the location, I have 4 columns with the header for the owner in each. Under each header is the owners' locations they own.
Column A in Locations Sheet
Row1 Col1 (Header) David Row2 Col1 Boston, MA
Then on the Data sheet is location sales for the month
Column A in Data Sheet Store 1 Store 2 Store 3 Store 1 store 5 store 7
Column B Store Owner (This is what I am trying to figure out)
So what I want is for it to show the store owner based on col1 (A) I know I could do multiple ifs to get this done but that's a hassle. I have 135 locations.
The store should match the column head in locations sheet and then show the owner for that location. Maybe it's late and I'm tired but I feel like this is simple.
How do I specify an array argument for trimmean function, when I am selecting from cells?
I am using
TRIMMEANformula in Excel. My input data cannot be selected into an array. This is what I would like to do: compute mean (with outlier removal) of six values in cells i2, i98, i194, i290,...
I have tried this
How do I create an array of the six values? Is there a way to use trimmean in this case?
Calculate percentage difference between cells based on date
- I have 4 cells: Cell 1, Cell 2, Cell 3, and Cell 4
- Each of the four cells have traffic on three days: 08-July-2019, 09-July-2019, and 10-July-2019,
- I obtained the total traffic for each using the formula:
- For each cell, I want to calculate the difference between traffic over those three days, i.e. difference between 08-July-2019 and 09-July-2019, 09-July-2019 and 10-July-2019.
I'm trying to calculate the difference between the traffic for each cell compared to the previous day.
I can't think of a way that it can be done. How can I accomplish this? Below is an example picture
Does the syntax of the "Calculate" function change between Power Pivot in excel 2010 and 2016?
The following formula has always worked correctly for me in Excel 2010 Power Pivot.
SUM_SALES:=calculate(sum([AYP Net Sales]),categories,locations,dates)
[AYP Net sales] refers to a column in the given sales table where SUM_SALES exists. 'categories,locations,dates' are references to other tables who are each linked by a single column respectively.
The effect of this was that slicers on my category, location and date tables would pass their filter context to the sales table. This has worked for the last two years.
I just upgraded to Excel 2016. Now if i try to recreate the same expression, i get the following error message:
The expression is not valid or appears to be incomplete. Please review and correct the expression. the syntax for '(' is incorrect. (calculate([SUM_SALES],categories,locations,dates)).
In addition, i have a number of expressions already written this way in the file. They calculate correctly, but any attempt to modify them, or add similar metrics results in the same above error.
I've tried rolling back to Excel 2010 and still get the same issue.
I'm after a fix, or an alternative method to achieve the same result.
Trying to Populate IE Input Box, Click Submit, then Retrieve a Value from IE
I am trying to automate the retrieval of domain name values from GoDaddy's valuation service located at: https://www.godaddy.com/domain-value-appraisal
I have done close to 400 manually but it is very tedious, error-prone, and it takes forever. I figured if I could create a VBA function in Excel 2010 then I could automate Internet Explorer. The IE version on the machine I would being this is IE 11.
I have tried several different suggested examples from various sites, including StackOverflow.com and I have not made any progress. I am stuck at how to populate the text box with a domain name from my spreadsheet. So what I did was just hard code a domain name for testing. I can whip up code that will loop through 400 rows later but right now I am trying to get just one to work.. I am messing up something with the IE DOM I think.
I cant even be sure that the rest of my steps are even coded correctly. The latest version of the code is below.
There are three steps to what I would like to automate: Step 1: enter a domain name from my spreadsheet into the input box at https://www.godaddy.com/domain-value-appraisal Step 2: click the "GoValue" button Step 3: return the estimated value back to a cell on my spreadsheet.
The code is below. Thanks if anyone can identify what I have done incorrectly and how to proceed correctly.
Sub Try3() Dim IE As New InternetExplorer Dim doc As HTMLDocument Dim objDomainIn As HTMLInputElement Dim objGoButton As HTMLButtonElement Dim objValueOut As HTMLSpanElement IE.Visible = True 'GO TO THE GODADDY VALUATION PAGE IE.Navigate "https://www.godaddy.com/domain-value-appraisal" 'WAIT FOR THE PAGE TO LOAD Do DoEvents Loop Until IE.LocationName = "Free Domain Value and Appraisal Tool | What is your domain worth? - - GoDaddy" Set doc = IE.document 'I WOULD LIKE TO DO THE FOLLOWING STEPS FOR SEVERAL HUNDRED DOMAIN NAMES. BUT FOR THIS EXAMPLE, HERE IS ONE OF NAMES: '1) insert the domain name into the "domainToCheck" textbox '2) click the "GoValue™" button '3) scrape the Estimated Value from the result page 'SO TRANSLATING THOSE STEPS TO VBA I TRIED: 'STEP 1) 'THE INPUT TEXTBOX ON THE FORM: '<input name="domainToCheck" class="domain-name-input searchInput form-control" aria-label="Enter a domain name" type="text" placeholder="Enter a domain name" value=""> Set objDomainIn = doc.all.getElementsByName("domainToCheck") '<----**** I AM STUCK HERE *** objDomainIn.Value = "MPGBooster.com" 'STEP 2) 'BUTTON THAT NEEDS TO BE AUTOMATICALLY CLICKED (I added carriage returns because it was very long): '<button class="btn btn-primary submit " ' type="Submit" ' data-eid="gce.sales.domain-value-appraisal.domain_search_marquee.domain_search_marquee_lp_module.button" ' data-creative-slot="e6a02371-14a2-4a21-b444-4aa0ae01b3b7" ' data-creative-name="domain-value-appraisal/Domain Search Marquee LP Module/Domain Search Block/Primary CTA" ' data-promo-name="domain_search_marquee.domain_search_marquee_lp_module.button8b002669-c11d-4913-881e-fe39fce24eab" ' data-promo-id="gce.sales.domain-value-appraisal." ' data-schema="add_promotion" data-tdata="module_id,e6a02371-14a2-4a21-b444-4aa0ae01b3b7^block_id,8b002669-c11d-4913-881e-fe39fce24eab^block_path,/ComponentSettings/GoDaddy/Sales/domain-value-appraisal/Domain Search Marquee LP Module/Domain Search Block/Primary CTA^campaign_name,^redpntcn,^redpntrid,^redpntcid,^redpntsn,^redpntrc," ' value="GoValue™">GoValue™</button> 'CLICK "GOVALUE" BUTTON TO GET DOMAINS VALUE Set objGoButton = doc.document.getElementsByClassName("input-group-btn") objGoButton.click 'STEP 3) 'THE RETURNED HTML SHOWING THE DOMAIN ESTIMATED VALUE '<span class="dpp-price price"><strong>$1,426</strong></span> Set objValueOut = doc.getElementsByClassName("dpp-price price") MsgBox "MPGBooster.com valued at: " & objValueOut.innerText 'CLEAN UP OBJECTS IE.Quit Set IE = Nothing
Excel shows inconsistent results for same SEARCH() function
Using SEARCH() function in Excel with exactly same arguments in different cells on same columns renders different results. There are 3 patterns and only one is correct- when actually all should show the same result.
In the Sheet called Result I'm using this:
The three pattern of the results are:
\#VALUE! 813 1
813 is the correct one and it shows the position of first encounter of the given string.
The file can be downloaded from here: https://www.mediafire.com/file/w1g9dkge31vor3g/test.xlsx/file ( test.xlsx file)
Why is this weird behavior and how can I correct it..?
If downloading the test file doesn't work - I'm using the following data:
1. wordsRange sheet:
A1: dummy1 A2: dummy2 A3: Friday A4: dummy4 A5: dummy5 A6: dummy6 A7: dummy7
2. searchedText sheet, A1 cell:
On Saturday, the now tropical storm made landfall along the northern Gulf Coast as a Category 1 hurricane, leaving tens of thousands across Louisiana and Mississippi without power and otherwise cut off from drier parts of the region. Areas of widespread flooding varied in severity with some seeing accumulations of 20 inches, while others escaped with substantially lower-than-predicted rainfall. In Mandeville, just 132 miles from Morgan City where the storm first made contact with land, the shore of Lake Pontchartrain expanded onto city streets. On Lakeshore Drive, photographer Scott Olson captured dozens of residents, many of them couples and families, as they explored the transformed — and largely underwater — portion of their shared home. The road, officially closed off to the public since late Friday, became the central hub for photographic evidence of the storm's power, even as city officials warned people to stay away. "Officials would like to encourage pedestrians that this is not a sightseeing event," reported local radio station KPEL 96.5, adding, "Your safety is the number one priority." While an official depth of the flooding along Lakeshore Drive has not been reported, many images show adults up to their knees in water, in spite of numerous flood gates attempting to keep the lake at bay. In addition to the dangers of fast-moving currents and waterborne disease, Louisiana residents have been warned of the presence of water snakes and alligators within floodwaters. "If the area you live in has high water, watch out for snakes and other critters who are trying to escape the floodwaters as well," noted fire department officials in Slidell, another Louisiana city less than 30 miles from Mandeville. Still, per Olson's photos, it would appear many Mandeville folks tried to make the best of a bad situation. In a stint of waterlogged romance, numerous couples posed for the photographer among the aftermath for what Olson dubbed a post-hurricane "date night."