for each loop

Greetings, I recently threw together a bit of code that parses text from the most recently modified file in a selected folder. However, the folder keeps track of job tickets at my work, and it can fill up fairly quickly. Right now, 889 .txt files are in the folder to be searched. I have...

Hi I am attempting to compare 2 columns in separate workbooks, and if a match is found, to return the data in columns 38-40 from one workbook into columns 38-40 of the second workbook. I thought the best way to do this was to progressively loop through the values of the column in WB1, and...

This is so simple, and it's making me feel like the newbie I am. What is the proper syntax for this example? Sub findthatvalue () Dim wks As Worksheet Dim Third As String For Each wks In Worksheets Third = [cell "H77" on whatever sheet the loop is currently looking at] MsgBox (Third) Next wks...

Hey guys so I have a large set of data. The macro I have right now looks up the name of the person then goes about 15 columns to the right which has people/ gift in the format X / Y, and the column after that has a list of ages Z. What I need to do is fix up the data so it says X People(Person)...

Hey! I'm quite new to VBA and would really appreciate some help to solve a problem I ran into. I have a dataset that looks like this: <tbody> NAME SCHOOL SCORE Lisa Johnny School 30 Inga Jimmy School 35 Johnny Carrie School 28 Beth Carrie School 32 James Johnny School 35 Sarah...

I have 24 months in my SaaS financial model and some costs have to charged on annual basis. I am unable to draw a formula that can be applied across all the rows consistently and only give me costs at the end of the year e.g. in months 12, 24, 36, and so on. Similarly, I have to apply a rate at...

Hello, I am trying to create a macro that will check each cell value in a specified range of cells to see if the cell value is less than or equal to the value in a static row and same column as the cell value that is being checked. If the cell value being checked is null nothing will change. If...

Excel 2010, VBA, Windows 7. intermediate VBA experience. I have a large spreadsheet that generates other spreadsheets from downloaded data. I am trying to optimize the coding so as not to keep repeating the same formatting code for multiple columns, for example. Kindly responses to prior posts...

I have just found something that seems strange to me. If I resize a range the address of that range does not get resized at the same time. This then prevents being able to step through the range using a For Each loop. Question: do I really have to SET the range so that I can loop through it...

I'm trying to find a way to get the last subfolder in a folder object without out using a “For Each” Loop. It seems to me that if the Folders Collection is stored in the memory so that in can be accessed through iteration, than there has to be a way to simply call one without looping through. I...

Hi All, I'm really stuck and would appreciate some help if any of you have five minutes to spare - my VBA knowledge is growing, but still limited. I am looking to have a user InputBox to enter sales for 6 weeks, as shown below: --- Sub InputUserData() 'This sub will create six messages boxes...

I can't seem to get syntax right to select column B when looping through collection of worksheets. Sub FormatAcrossSheets() Dim currentWS As Worksheet For Each currentWS In Worksheets currentWS.Columns(2).Select 'fails: error "Select method of Range Class failed"...

Hello experts, I have a list of dates and I only need the month of each date. I am still learning on VBA so please bear with me. Below is the code I have written but it seems that it is only extracting the month of the first cell. Where have I done wrong? Also, I may need to increase the...

Having a problem with the VBA error "Cannot change part of a merged cell" Here is my code. It clears the range on the first sheet, then hides the Raw Samples sheet. The loop then clears the ranges on the unhidden sheets in the workbook because they are formatted the exact same. This code only...

Hi Guys, I've a chart built out of sales data. There are 3 columns Selection, Category and Sales for year 2001 to 2014. I need the chart populated dynamically based on the selection column. I'm using the below code. Please let me know what I'm doing wrong here. I'm pretty new to VBA and...

I have a macro with 1 sub that calls 52 individual subs if a certain checkbox is clicked. I am wanting to create a loop that will get rid of having to have 52 similar but not identical subs (one for each row of data). I need the loop to check and see if each one of the checkboxes are checked...

Good Morning, I have two listboxes that are dynamically populated at runtime so the user can add items. I want to add an Uppercase converserion to the text before these items are displayed Since the number of items can e different every time. I'm trying to use listbox.Itemscount as my loop max...

Hi everyone, this is my first post so I apologize in advance for any mistakes I can make posting. Here's my problem: I wrote a macro to loop through a range of cells and then, if the cells' value is more than 0, to copy the cell and the adjacent ones to another sheet. The problem is that it...

Greetings, I wrote a chart formatting macro, which works when applied to a specific sheet, and I'm trying use it in a for each loop. The macro is supposed to loop through a list of sheets and only format chart 1. I'm getting a "run time error 438: object doesn't support this property or...

Can anyone tell me why this clears the contents of each row correctly: For Each c In Range("B9:B" & Range("B" & Rows.Count).End(xlUp).Row) If c.Value <> Range("A2") Then c.EntireRow.ClearContents Next c But this does not delete the proper rows: For Each c In Range("B9:B"...