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
Jenkins - VBScript - Excel: Excel does not become visible when the vbscript is triggered via Jenkins
I have written a simple VBScript code which:
- opens an excel workbook
- makes the excel workbook visible
- update the contents of Cell(1,1) of Sheet1 with the current system time
- saves the excel workbook
The code is as follows:
Option Explicit Dim objExcel, objBook, objSheet, strPath strPath = "C:\Users\a614923\Work Repository\Local\Test Complete\Working\05012019\bonn-6.11.9_Regression\FIL_REGRESSION\Stores\Files\ExternalDriver\Test.xlsx" set objExcel = CreateObject("Excel.Application") objExcel.visible = true objExcel.displayAlerts = false set objBook = objExcel.Workbooks.open(strPath) set objSheet = objBook.Sheets("Sheet1") objSheet.cells(1,1) = "Time: " & time objBook.Save 'objBook.Close 'objExcel.Quit
When I run this VBScript file(directly by double-clicking, not via Jenkins), all the 4 steps mentioned above run fine. When I run the same file via Jenkins, the following happens:
- excel workbook is opened
- the contents of Cell(1,1) of Sheet1 are updated with the current system time
- excel workbook is saved
The issue is that the excel workbook does not become visible. I have been stuck on this step for hours now. Today is the first time I am using Jenkins. Is there any Jenkins configuration that I am missing? Has anyone ever faced this issue?
I am running windows batch command on Jenkins to achieve the same:
C:\Windows\SysWOW64\cscript.exe "C:\Users\a614923\Work Repository\Local\Test Complete\Working\05012019\bonn-6.11.9_Regression\FIL_REGRESSION\Stores\Files\ExternalDriver\TEST.vbs"
This is how my Build Console looks like after running the build:
Started by user Gurmanjot Singh Building in workspace C:\Program Files (x86)\Jenkins\workspace\SuiteRunner [SuiteRunner] $ cmd /c call C:\WINDOWS\TEMP\jenkins2976471610072523635.bat C:\Program Files (x86)\Jenkins\workspace\SuiteRunner>C:\Windows\SysWOW64\cscript.exe "C:\Users\a614923\Work Repository\Local\Test Complete\Working\05012019\bonn-6.11.9_Regression\FIL_REGRESSION\Stores\Files\ExternalDriver\TEST.vbs" Microsoft (R) Windows Script Host Version 5.8 Copyright (C) Microsoft Corporation. All rights reserved. C:\Program Files (x86)\Jenkins\workspace\SuiteRunner>exit 0 Finished: SUCCESS
Any help/suggestion would be appreciated.
Python 3.6 - Not Closing the Workbook on xlwb.Close
I am using Python 3.6 and I am opening an workbook using Workbooks.Open method from Dispatch library.
I am not able to close the workbook. If I go into Task Manager, I could see that the Excel workbook is still open, the file is heavy and it slows down the system.
And also on successive runs, when the file gets updated - it is not showing the updated data on the result. If I close the file in the Task Manager manually and then run the script, it shows the updated result.
Therefore, I removed every line and I am just opening the file and closing it. Even then, the file is not getting closed and it can be seen in the Task Manager.
Here is the code I am using :-
from win32com.client import Dispatch InputFileName = "\\\ServerA\NetB\Folder\Input Details.xlsx" xlApp = Dispatch("Excel.Application") xlwb = xlApp.Workbooks.Open(InputFileName) xlwb.Close(SaveChanges=False) xlApp.Application.Quit()
VBA Excel include a IF statement in email form
I can't seem to include a IF formula in my code, in the part where the email is being sent to some people included in column R2 of my Excel file
I would like to exclude from
email@example.com this user is already in column R2 (so in
.To). How could I write the code? Otherwise, my user
firstname.lastname@example.org receiving the email twice, once because the user is in
.Toand once because he is in
.To = Worksheets("MyData").Range("R2").Value .CC = "email@example.com; firstname.lastname@example.org; email@example.com" .BCC = "" .Subject = "TEST" .Body = "Hello" .Attachments.Add ThisWorkbook.Path & "\ME\" & Check & " " & Format(Now, "dd-mmm-yy") & ".xlsx" .Display '.Send
What could be the code to do that?
Delete rows based on cell value not working
I have some data in sheet called New, and my data are in column A to column K. However, column E to H are intentionally left blank for data analysis purposes and I have no header so my data starts from cell A1. Now in column A we have color in cell, I would like to delete any rows that aren't white so keep rows that don't have color in it.
I did some research but all of the codes I got online either delete the whole sheet or just pass through codes and nothing happens. Below are the ones I am currently using that doesn't do anything. I use F8 and still no error.
See image for my sample data and I am trying to get the results with cells that don't have any color in it. I tried to remove quotation mark for the color index but still it doesn't work.
Sub deleterow() lastRow = Worksheets("New").Cells(Rows.Count, "A").End(xlUp).Row For i = lastRow To 1 Step -1 If Worksheets("New").Cells(i, 1).Interior.ColorIndex <> "2" Then Rows(i).EntireRow.Delete i = i + 1 End If Next I End Sub
How to attach all files in folder to an email - vba code
I have the following code in vba, all works well but i need to change to attach all files in selected folder (the way it is now i have to write the name of said attachment). Unfortunately i'm a noob when it comes to vba programming.
Sub Send_Files() Dim OutApp As Object Dim OutMail As Object Dim sh As Worksheet Dim cell As Range Dim FileCell As Range Dim rng As Range With Application .EnableEvents = False .ScreenUpdating = False End With Set sh = Sheets("Sheet1") Set OutApp = CreateObject("Outlook.Application") For Each cell In sh.Columns("A").Cells.SpecialCells(xlCellTypeConstants) Set rng = sh.Cells(cell.Row, 1).Range("D1:Z1") If cell.Value Like "?*@?*.?*" And _ Application.WorksheetFunction.CountA(rng) > 0 Then Set OutMail = OutApp.CreateItem(0) With OutMail .To = sh.Cells(cell.Row, 1).Value .CC = sh.Cells(cell.Row, 2).Value .Subject = "Decont UTA" .Body = sh.Cells(cell.Row, 3).Value For Each FileCell In rng.SpecialCells(xlCellTypeConstants) If Trim(FileCell.Value) <> "" Then If Dir(FileCell.Value) <> "" Then .Attachments.Add FileCell.Value End If End If Next FileCell .Send 'Or use .Display/Send End With Set OutMail = Nothing End If Next cell Set OutApp = Nothing With Application .EnableEvents = True .ScreenUpdating = True End With End Sub
Excel VBA Run-Time Error 424 Object Required
I'm trying to create a client intake form that allows the user to input information to be stored in another sheet for use later.
I've written this code and get a Run-time error '424' when I run the form. I've run out of ideas so I'd appreciate any help!
Private Sub OKButton_Click() Sheets("ClientList").Activate NextRow = Application.WorksheetFunction.CountA(Range("A:A")) + 1 Cells(NextRow, 1) = TextFirstName.Text Cells(NextRow, 2) = TextLastName.Text Cells(NextRow, 3) = ComboCaseStatus.Text 'potential bug Cells(NextRow, 4) = TextAdminFirstName.Text Cells(NextRow, 5) = TextAdminLastName.Text Cells(NextRow, 6) = TextAdminPhone.Text Cells(NextRow, 7) = ComboAdminRelation.Text 'potential bug Cells(NextRow, 8) = TextStreetAddress.Text Cells(NextRow, 9) = TextCityStateZIP.Text Cells(NextRow, 10) = TextPhoneNumber.Text Cells(NextRow, 11) = TextMailingAddr.Text Cells(NextRow, 12) = TextEmail.Text If OptionRetainer Then Cells(NextRow, 13) = "Retainer" If OptionPOA Then Cells(NextRow, 14) = "POA" If OptionAuthorization Then Cells(NextRow, 15) = "Authorization" If OptionIntakeForm Then Cells(NextRow, 16) = "Intake Form" Cells(NextRow, 17) = ComboStaffEnter.Text Cells(NextRow, 18) = Date TextFirstName.Text = "" TextFirstName.SetFocus TextLastName.Text = "" TextLastName.SetFocus TextAdminFirstName.Text = "" TextAdminFirstName.SetFocus TextAdminLastName.Text = "" TextAdminLastName.SetFocus TextStreetAddress.Text = "" TextStreetAddress.SetFocus TextCityStateZIP.Text = "" TextCityStateZIP.SetFocus TextPhoneNumber.Text = "" TextPhoneNumber.SetFocus TextMailingAddr.Text = "" TextMailingAddr.SetFocus Call SortDataDown End Sub
Removing rows from excel
Good Morning, I am trying to work with the formula =IFERROR(INDEX(Input!C$4:C$9,SMALL(IF((Input!$C$4:$C$9<>"")*(Input!$C$4:$C$9>0),ROW(Input!$C$4:$C$9)-ROW(Input!$C$4)+1),ROWS($1:1))),"")
to remove the blank rows and zeros , for the form I am attaching the sheet can you please help and update if column C5 equal a zero or a blank row remove the row from the spread sheet.
This is the spreadsheet link
Excel wont update formula when dragging
Not something id usually post here but google doesn't want to reveal the answer to easily!
I've created a formula for my excel doc on the first row but when I drag the formula down to cover additional rows it still uses the values from the first row.
so on the first row, my formula is;
and i would expect it to update on each line like;
but this doesn't happen, everything refers back to the first row, R2 in this case.
I've tried the usual switching auto calculations off and on and have reset the cell format to general for all cells which have no effect.
Im assuming there is something in my formula that is stopping this from happening rather than excel not working correctly??
COUNTIF & MATCH with additional Parameter
This is a follow up to my post Combining COUNTIFS with a VLOOKUP
Attached are examples of my 2 worksheets
This contains the data, please note some columns have been hidden for anonymity purposes. This sheet is very large spanning A:XA
This is an overview extracting and calculating data from Tracking! Again some columns are hidden.
What I am having problems with is bringing back the counts of "WB" for each category, such as Focus, Participation etc. I can get the Total easily enough using:
I tried changing this to
As I need it to match the Student ID across both sheets and then match the category (Sheet1! X2:AE2) and then count the number of "WB" that exist within that category across Tracking!
Any help appreciated as I have tried numerous ways to try and get this to work, but just can't.
How to capture shell WScript Shell output from Excel VBA without using cmd?
I have used the various posts similar to this and come up with the following, but not sure why this doesn't work for me. I have a UserForm wherein I am trying to run a script and capture the output, including any errors. However, the console window quickly disappears as soon as I run this code:
Sub test() Dim wsh As Object Set wsh = VBA.CreateObject("WScript.Shell") Dim waitOnReturn As Boolean: waitOnReturn = True Dim windowStyle As Integer: windowStyle = 1 command = "python ""C:\test1.py --arg1=ABC"" > ""C:\logs\test.log""" wsh.Run command, windowStyle, waitOnReturn End Sub
The script runs just fine by itself until I add the redirection ('>'). My python script needs to be launched with 'python' keyword preceded in the command as shown above and so I cannot use 'cmd /c'. Is it possible to capture the output without using 'cmd /c' and while using wsh.Run (preferably) instead of wsh.Exec?
not able to upload xlsx or docx file in laravel 5.7
Hi I'm trying to upload a xlsx file to the laravel api, (I am able to upload (images, xls) files), but I'm not able to do so I am getting the below details as follows
response for the
$file = request()->file('user_bulk_upload'); var_dump($file);
object(Illuminate\Http\UploadedFile) private 'test' (Symfony\Component\HttpFoundation\File\UploadedFile) => boolean false private 'originalName' (Symfony\Component\HttpFoundation\File\UploadedFile) => string 'sudo1.xlsx' (length=10) private 'mimeType' (Symfony\Component\HttpFoundation\File\UploadedFile) => string 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' (length=65) private 'error' (Symfony\Component\HttpFoundation\File\UploadedFile) => int 0 protected 'hashName' => string 'jFWNUFGJVuAMNi8p9BdzuZmKXu2MA5RtiOpopBPO' (length=40) private 'pathName' (SplFileInfo) => string 'C:\xampp\tmp\php2472.tmp' (length=24) private 'fileName' (SplFileInfo) => string 'php2472.tmp' (length=11)
$path = $file->store('excel', 'public');var_dump($path);
I'm getting response as
Can anyone help me with this.
How to identify unique and duplicate data in multiple rows across 3 columns using formula
I have the following data: Column A Column B Column C AA AC AD AC BK DD AA AC AD CC CA CA CC BA CC
Now, i need a formula to identify Row 1 and Row 3 as duplicates without adding an additional column for the same.
I have already tried concatenating Column A, B and C and then using Countif to identify duplicates, but i do not want an additional column. Is there a way out?
Thanks in advance