#vba

I need to add a blank row before when value in B changes, in the example file below, a blank row will need to be added after the highlighted cells. How do I approach this issue with VBA? Note: the value in column B can be any length, sometimes it is just a number, sometimes it is a combination...

I need to modify a bunch of excel files, for any row that contains the word "Skimmer", I need to move the value from column B (same row) to in front of Skimmer (always in column K) like this: (1)Skimmer, (2)Skimmer, (3)Skimmer. (The value in column B does not need to be removed) see below for...

Hi all, I have an excel file used to generate ~15 tables of data. This report needs to be generated every week and I wanted to automate the 'from excel to ppt' updaiton. I am using the below code. it works for the active sheet, but how can I make the macro to go to the next sheet, copy paste a...

Hi All, I've been busy creating a code that, depending on whether the values in a column are greater/less than 0, the code will copy those values to the bottom of the table and paste them into a corresponding table. The problem Im having is that Ive had to repeat this code for each column and...

part of my macro has a simple equation n /6, when n is < 6, the result is rounded down to 0. is there any way I can have this result always roundup? .Rows(lrNewRows) = Array(Cells(lr, "A"), ".", n \ 6, "F51114", "No", " ", " ", " ", "Purchased", " ", "Primer") Sub WrapidSealNine() Dim...

I have this code to count the number of letter "R" in any given row, see test file below, these two rows contain 2x "R" but my code is spitting out 3 every time... why is that? code below: Dim ws1 As Worksheet Dim lrNew As Long, n As Long, n1 As Long, n2 As Long, sr As Long Set ws1...

I need to count the number of letter "B" in a cell when "CS55" and "B" are present the way I am counting the letter is: length of the string - length of the string without any letter B = the number of letter B code below, error message pops up every time I run it "Type mismatch" Dim lrNew As...

in this macro, I am using the CountIf feature to count the # of letter "B" in column K when "CS55" is in column K ws1 is defined, why the error message ""method or data member not found" ? n = ws1.CountIf(Range(lrNew, "K"), "*B*") full code below Sub PaintCS55() Dim ws1 As Worksheet, lrNew...

part of my macro has this equation: it counts the number of Base, Riser, Cone, TS ( these are in column K), if column O contains Sanitary. What if I want to add one more variable where this equation counts These items ONLY when there is also the number 4 present in column K ? any help is...

I need to calculate the amount of CS55 in a bunch of excel files. When column K contains CS55 and Black, we use this equation or if column K contains CS55 and two letter "B" use: n = n1 * 0.000666 * 7.48 * 2 when column K contains CS55 and one letter "B", use: n = n1 * 0.000666 * 7.48 full code...

I have this macro to calculate the gallonage of paint (CS55), it adds an additional row to the end of the excel file to display the gallonage This part of the macro does the calculation. the "2" in the equation means two layers. .FormulaR1C1 = "=SUMIF(R2C11:R" & lr & "C11,""*CS55...

When column M contains "GALV" (any row in a file), I need to add "GALV" to the end of the value in column K (same row) why isnt my code below working? any help is appreciated. Test excel file also attached Sub Galvanized() Dim lr4 As Long Set rng2 = Range("A1").CurrentRegion lr4 =...

part of my macro is to do a tabulation of a certain item when column K contains keywords "9"" and Wrapid-Seal", counts the total qty if K does not contain the keywords then qty = 0 every time I run this macro, qty is always zero, even know the excel file indeed have this item in K. Why is that...

I need add 3 rows to the end of an excel file, and fill columns A-D with certain values I know the code below can add one row and fill them with values lr = Cells(Rows.Count, 1).End(xlUp).Row Public lr As Long, lr2 As Long lr2 = lr + 1 Cells(lr2, "A") = "One" Cells(lr2, "B") = "Two"...

I need my macro to calculate the quantity of item "evergrip", the calculation goes like the following: It calculates the total number of "Base", "Riser", "Cone", "Top slab" with the keyword "Sanitary" in column O. the qty of these items are always in column C, the description of these items are...

I have this macro that add a row after the last row that contains data, and populates this new row with values in different columns. see code below, it works perfect when a file contains the keyword "Rainguards", but if a file does not contain this keyword, the following error message pops up...

I have this code that counts the qty of a certain item, adds a row at the end of the excel file to show the qty If the total qty adds up to 0, the code removes this new row, what if I just want to remove the value in column D in the new row instead of removing the entire row when the value is 0...

I am still fairly new to VBA. I have been using S & LRGLOBAL throughout the rest of my Macros and would like to also use it for updating my pivotchart sources. However, I can't get it to update the chart even if I name it "S266", so I think it may be another part of the code that is stopping it...

I need to offset the first 3 characters in column O to the left, add them infront of the existing text in K (with parenthesis) ONLY WHEN the value in column F is P1 so in the example below, row 2, 7, and 14 have P1 in column F, after I run the macro column F on these rows will look like: (D B)...

Hi All, I'm currently experimenting with unprotecting and protecting workbooks using VBA but have been unsuccessful in getting it to work for one of my workbooks. In row 1 which is a hidden, fully formatted blank row, I'm trying to unprotect a portion of that row (BP1:DR1), copy and paste...