I have a worksheet and it has some information on it (ill show an example in a min). will all the information i need is in one column. there is only the name of the item that i need the rest can be deleted. like so ........
View 12 RepliesI have a code that inserts a new column after every 7th column. I want to include a formula where every 7th column value is subtracted from the values present in the column before the 1st, or you can think of it as subtracting 7th column of the present group from the 7th column of the previous group. Example: The range of my data starts from col F, then
F (7th) New Column (G) H (1st) I (2nd) J (3rd) K (4th) L (5th) M (6th) N (7th) New Column (O) P (1st) Q (2nd) R (3rd)
So, New Column (O) = N - F
and the next New Column (W) = V-N ...
NOTE: Column G can be ignored.
I want to add a looping function to this so that it will continue to subtract for the other respective columns as well. How do I incorporate this into the following code?
[Code].....
I'm using Excel 2013.
on the syntax of things in VBA. here's a description of what i'm trying to do:
1) loop through a particular column ("N"), and compare the values (if not blank) with corresponding cells in another column ("V"). i.e. - comparing N1 to V1, N2 to V2, etc...
2) run If, ElseIf statements to display appropriate values in different column ("O"), but on the row that corresponds to the row the values in Step 1 are compared. i.e. - if N1 > V1 then cell O1 = value; if N2 > V2 then cell O2 = value; etc...
here's an example (but obviously not in proper syntax). hopefully someone can help me convert it to Excel VBA:
Function CalculateFR()
Dim Col1 As Column = ColumnN
Dim Col2 As Column = ColumnV
Dim Col3 As Column = ColumnO
Dim Cell As Cell
Dim IndexValue As Integer
For Each Cell In Col1
If Col1 > Col 2 Then
IndexValue = 5
Else If Col1 < Col2 Then
IndexValue = 4
End If
Next Cell
If Not IsEmpty(Cell) Then Col3 = IndexValue
End Sub
I had a problem finding something then deleting the column, I tried to alter the code from that thread to solve a new problem and I am close (I think), but not quite there. I need to find the column "Group" and merge it with the column "Sex" which is offset by 1 column. This code will go through and find the column "Group" and merge for the first cell, but will then get stuck in a loop just concatenating "Sex" to the end continuously. I need it to concatenate both columns entirely together. Any simple fix to what I modified? Previous thread should you be interested:Find Part Text On All Sheets & Delete The Column.
View 9 Replies View RelatedI am trying to loop through columns and rows to get the sum of each and delete that column that equals zero. I have-
Dim lastrow
Dim r, c As Integer
Dim RowSum
RowSum = 0
ActiveSheet.UsedRange.Select
lastrow = Selection.Rows.Count
For r = 2 To 80
For c = 2 To lastrow
RowSum = RowSum + ActiveSheet.Cells(r, c).Value ' here I get error Application-defined or object-defined error
Next c
If RowSum = 0 Then
ActiveSheet.Cells(r, c).EntireColumn.Delete
End If
Next r
What am I doing wrong?
The below code is supposed to loop through column a and create a copy of a worksheet change the name of the worksheet to the value found in column A
I need change the loop so it starts at A8 and Ends at A59. I have no Idea how to do this.
Sub AddSheets_FromList()
Application.ScreenUpdating = False
'assumes starting macro on first value in date list
Dim ListCount As Integer
Dim NewSheet As String, LastSheet As String
LastSheet = "Weekly Results"
ListCount = 0
im looking for a macro that will take away the free stock value from the values in the following columns until the value reaches 0 then subtract from the next column with a value in it until there is no more left in the free stock to subtract. if the value reaches 0 it can be blank.
so for example as shown in the image attached cell b2 shows 150 in free stock, then the next column with a value in is d2 which shows 100 so takeaway until this reaches 0 which leaves 50 and this can be shown as blank now, then move to the next column which is e2 and this shows 30 so this can now be blank also, finally f2 shows 50 so minus the remainder from the original 150 which 20 and this should now be 30.
my data is in a pivot table so a paste special to another sheet may also be necessary. The amount of columns could also vary due it being a pivot table.
I have created a macro that loops through column 4 and gives the value of 2 other cells acording to what is found.
The problem comes when the macro only works sucecefully if i click on a cell in that column. Therefore i think the macro is not doing this.
Im sure that i have used the right code does anyone know what is wrong.
What the easiest way of looping though the values in a column in a macro? How to you "call" the cells in the macro?
View 4 Replies View Relatedhow to write a for loop for this coding.
considering x as row,
i want to check the value in column1 for n rows.
Code:
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
Dim rw As Integer
Dim LastRow As Integer
LastRow = Sheet8.Range("B16:B300").End(xlUp).Row
For rw = 16 To LastRow
[Code] ........
Cannot get the loop to run. I am trying to make it so the loop will stop when it reaches the last non empty cell.
I'm trying to build code to loop through each column of data and colour the cell red if it is below x.
Problem is each column has a dynamic number of rows, it changes, and I have a dynamic number of columns as well.
I currently have code to do it on 1 row, but am trying to loop through all filled columns with no success, heres the code:
Code:
mycol = Cells.Find("", SearchOrder:=xlByColumns, _ LookIn:=xlValues, SearchDirection:=xlPrevious).Column
MsgBox mycol
[Code]....
I have a number of strings in adjacent columns in the same row, like so.
Coulmn1 Column2 "blank" Column3
If the cell is blank I want to hide the respective column .
I'm guessing the code will roughly take the form of the code below with an if statement nested in a for statement.
Sub (test)
With Range("BD22")
For i = 1 To 10
if range (i,22) = "" then hide
else don't hide
.Offset(1, 0).Formula
Next i
End With
I need to create a loop within a loop. First loop works fine as follows:
-------------------------------
x = 0
Dim DirArray(0 To 80) As String
Dim intI As Integer
For intI = 0 To 80
DirArray(intI) = Range("start").Offset(intI, 0).Value
Next
For x = 0 To 80
lob = DirArray(x)
Range("lob") = lob
Calculate
Sheets("WBB LOB Summary").Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Next
----------------------------
At the 'start' point, first loop works downward through rows. Second loop would work across columns in each row within the first loop. I will have to use a variable for 'y' (the across value) b/c the number of cells containing data can vary between 3 & 9, but I have a COUNTA in each row setup to use as the variable.
So spreadsheet data would look like this with A being the 'start' cell:
A 1 2 3
B 1 2 3 4 5 6
C 1 2 3 4
I need to pickoff A1, A2, A3, B1, etc, picking up every sub-category on the sheet.
I have two worksheet. One worksheet (ws1) contains a list of item I want. The other sheet (ws2) contains multiple columns where the header (row 6) is named by item name.
I have the following code which deletes the entire column if the header name is not in the list contained in ws1 :
VB:
Sub delete_col()
Dim wanted As Boolean
Set ws1 = Workbooks("test1").Sheets("aaa")
[Code]....
First of all, this loop does not work properly since deleting the entire column shift them on the left, so when I first analyze column 11, if I delete it and then analyze column 12, the real column 12 now became column 11 and so on...
Secondly, this code is pretty slow. I am pretty sure I don't have to loop through my initial item list everytime I do Instr on a new column.
I have a workbook with 180 Sheest. I need to copy sheet name and paste to column Name. In the Column Year write 2013.Finally I need to all sheets as show below in in Sheet Farms. Doing this one by one is time consuming and with error risk. I think a loop can do this,nevertheless, I don't Know how to do it. Column size can be different in each Sheet
Sheet name Famr1
CodNameYearDescTotal1Total2ProvCnt
1234Apples200xxxyyyzzz
3412Bananas400xxxyyyzzz
2358Oranges500xxxyyyzzz
8956Pines800xxxyyyzzz
[Code]....
I am trying to use a code to run a loop and search in column B for a certain name and if it finds it I want it to then look at column P and if it finds that the result in column B is less than $1000 then I want it to cut the entire row and paste the row in another sheet. Here is the code.
View 4 Replies View RelatedI have Created the below macro, however I cannot figure out how to make it loop so that is continues to copy the next column over ( so go from column E:E , To Column F:F, and then To Column G:G), and paste the data into the same column (D:D or Range paste_range) everytime, and print.
Sub paster_print()
'
' paster_print Macro
'
'
Columns("E:E").Select
Selection.Copy
Application.Goto Reference:="paste_range"
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.Goto Reference:="Print_Area"
Application.CutCopyMode = False
End Sub
I want a marco that can "find & replace" in a column, let's say K. Let's just say I want to find "AAA" and replace "BBB" and "CCC" and replace "DDD", I just need the code so I can change it myself. It will be more than 40+ replacement.
View 9 Replies View RelatedI have a range of cells which I wish to print to a .txt document. However, I would like these cells to stay aligned, one on top of the other. I am currently doing that by finding the cell with the widest piece of data for each column, and storing the width that each column needs to be to an array of integers. Then, when printing out the range, I simply add spaces to each piece of data its width is the same as the max column width. I am finding the max column width using the following loop:
'find the width for each column and store in col_width()
For cur_col = 1 To total_cols
'skip the tag switches column
If cur_col <> 3 Then
max_data_width = 0
For cur_row = 1 To total_rows
cell_data_width = Len(Find_String_Diagnostics(diagnostic_range.Cells(cur_row, cur_col)))
If cell_data_width > max_data_width Then.................
I want to make a vba (macro) for excel that will look at the column to the left of an active cell and if there is a formula there drag it over to the right into the active cell and if there is not a formula there copy and paste value of what is in the active cell into itself deleting any formulas that may have been there. on top of that if the cell to the left of the active cell has no boarding I need to copy it and paste format to active cell. I need it to continue a number of times to be decided in cell A1 and just go down the column doing this. Please help, I know it sounds hard but I need it for 10,000's of lines of code and really cant do it by hand ...
View 8 Replies View RelatedI am trying to create a loop in which the code "reads" ... lets say... cell F5. Determines if cell F5 = 1. if not, it moves on. only, instead of moving to cell F6, it moves to cell G5. so i need to add one to the column letter. How is this best achieved? having trouble with this. adding a letter to it concatinates them, and adding numbers, well its a mismatched type so that simply does not work.
View 3 Replies View RelatedI built a Microsoft Access database for my church to track member information. I was able to import a lot of the needed information to create the records for each individual and household easily. What I can't do is import their offering donations until it is "Normalized." Which means that I need each row to be 1 record with the fields listed as the column headings. My church has a spreadsheet with several sheets one of which is named Input. I've attached the desensitized file for you to view/play with.
What I need to do is loop through the input sheet and find all the values in range B:4 to GN:387 that are greater than zero or not null (I believe they entered the default as zero so their other calculations would work). Then I need those values to be transferred to the column labeled Offering Amount on the ImporttoAccess sheet. Somehow I also need to define three variables associated with the cell that is contains a value greater than zero.
1. I need the row heading value for the envelope number (Column A of input sheet) which intersects with the amount
2. I need the column heading value (3 field merged) for the date (Row 1 of input sheet) which intersects with the amount
3. I need the column heading value (labeled: GenFund, WLA, or Missions) for the designation of the offering (Row 2 of input sheet) which intersects with the amount
Each of these I need to attach to the value offering amount and insert into their corresponding column fields for that record. I have entered 2 example rows of data into the ImporttoAccess sheet for you to see what I am after. I am sure it's possible since the data is there, But how to go about it.
I have several Excel files that I am about to change some data in, so I need to create an macro to speed it up. All sheets should be updated besides the last one.
The number of sheets differs between the files. The data that should be changed is in column C, starting at cell C10. But the number of rows differs. If the data in the cell begins with a Q, the Q should be removed, and if it doesn't begins with Q it should be skipped.
I am trying to loop through Column Q2:Q2000, and divide the adjacent Row R2:HS2 by the value in the corresponding Q column.
ie. cells R2: HS2 should all be divided by Q2
cells R3:HS3 should all be divided by Q3
etc until R2000:HS2000 are divided by Q2000
I presume that i would require an initial loop to scroll through the column and a seperate loop to scroll through the row values, but i cannot get my head around how this is done.
make and loop that loops true all values in column A and B. For example, if the cell A3 is higher the cell B3 then I want it to write the higher value in new sheet.
View 2 Replies View RelatedI have a pivot table with two columns: Code and Quantity. I want to loop through the Quantity column and hide all values = 0. I can do this easily by incorporating this into my code:
Code:
pt.PivotFields("Code").PivotFilters.Add _
Type:=xlValueDoesNotEqual, DataField:=pt.PivotFields("Sum of Quantity"), Value1:=0
However, I have a "(blank)" code in all pivot tables. I do this because occasionally there is no data to organize in a pivot table. Having a "(blank)" option will allow my code to work even when there's no data. Therefore, I want to keep blank visible at all times. The above code hides "(blank)", however.
The below code is what I've tried with no success.
Code:
Set pf = pt.PivotFields("code")
For Each pi In pf.PivotItems
If pi.PivotFields("Sum of Quantity").Value = 0 Then
[Code] ........
What I have is 3 columns, all containing numbers
What I want to do is delete a row if the middle column is less than 1.
However my loop seems to skip a row if the column data is like the below
a
b
c
1
1
1
1
0
1
1
0
1
1
1
1
1
0
1
End
Code:
Range("A2").Select
Do
If ActiveCell.Offset(0, 1) < 1 Then
Range(ActiveCell, ActiveCell.Offset(0, 2)).Select
Selection.Delete Shift:=xlUp
End If
ActiveCell.Offset(1, 0).Select
Loop Until ActiveCell.Value = "End"
I am trying to Find a cell containing a string ("derf" in this case). Then bold the entire row and continue to loop till it hits the end of the row and column.
Here is what I have.
Sub Macro3()
Range("B65536").End(xlUp).Select 'Finds the bottom in column B and sets it to endhere
ActiveCell.Value = "endhere"
Range("A1").Select 'goto the top
Do Until ActiveCell.Value = "endhere" 'The Do Loop Starts
Rows("1:30").Select
Set rfoundcell = Selection.Find(What:="derf", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
With rfoundcell
rfoundcell.EntireRow.Font.Bold = True
End With
Loop 'Keep looping till it hits the bottom of the columns row!
End Sub
i had a database from which i need to extract data from COlUMN B, select the
View 2 Replies View Related