Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find next blank column with Macro
Hi Guys
I have a question you may find a bit daft but it has me stumped. I have a macro that finds the data I am looking for and pastes it into a new worksheet ok but what I would like it to do is look along the columns and find the next blank column and paste the data there so that with each paste the macro will paste the new data into the next blank available column. so first past will be in column A, then next paste in column B then next in C and so on I have this code at the moment but all it does is find the same column and over write the data that is already there Dim LastCol As Long LastCol = Sheets("Finds").Range("IV1").End(xlToLeft).Offset( 0, 1).Column Ive searched through all the posts and have found it very very helpful for other things but cant find anything on this Thanks for you help in advance SAMIJLeeds (West Yorkshire, UK) |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find next blank column with Macro
Try some code like the following:
Sub AAA() Dim WS As Worksheet Dim WhatRow As Long Dim FirstUnusedColumnNum As Long Dim FirstUnusedColumnRng As Range Set WS = Worksheets("Sheet1") '<<<< CHANGE AS DESIRED WhatRow = 1 '<<<< CHANGE AS DESIRED With WS Set FirstUnusedColumnRng = .Cells(WhatRow, ..Columns.Count).End(xlToLeft) If FirstUnusedColumnRng.Column < 1 Then Set FirstUnusedColumnRng = FirstUnusedColumnRng(1, 2) End If FirstUnusedColumnNum = FirstUnusedColumnRng.Column End With Debug.Print "First used range: " & FirstUnusedColumnRng.Address, _ "First unused column number: " & FirstUnusedColumnNum End Sub -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting www.cpearson.com (email on the web site) "samijleeds" wrote in message ... Hi Guys I have a question you may find a bit daft but it has me stumped. I have a macro that finds the data I am looking for and pastes it into a new worksheet ok but what I would like it to do is look along the columns and find the next blank column and paste the data there so that with each paste the macro will paste the new data into the next blank available column. so first past will be in column A, then next paste in column B then next in C and so on I have this code at the moment but all it does is find the same column and over write the data that is already there Dim LastCol As Long LastCol = Sheets("Finds").Range("IV1").End(xlToLeft).Offset( 0, 1).Column Ive searched through all the posts and have found it very very helpful for other things but cant find anything on this Thanks for you help in advance SAMIJLeeds (West Yorkshire, UK) |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find next blank column with Macro
I use:
lastCol = Sheets("Find").Cells(1, Columns.Count).End(xlToLeft).Column my first row always has data, hence the cells(1 portion, yours may not, this gets the last column, to paste in the next just put lastCol+1 -- -John Please rate when your question is answered to help us and others know what is helpful. "samijleeds" wrote: Hi Guys I have a question you may find a bit daft but it has me stumped. I have a macro that finds the data I am looking for and pastes it into a new worksheet ok but what I would like it to do is look along the columns and find the next blank column and paste the data there so that with each paste the macro will paste the new data into the next blank available column. so first past will be in column A, then next paste in column B then next in C and so on I have this code at the moment but all it does is find the same column and over write the data that is already there Dim LastCol As Long LastCol = Sheets("Finds").Range("IV1").End(xlToLeft).Offset( 0, 1).Column Ive searched through all the posts and have found it very very helpful for other things but cant find anything on this Thanks for you help in advance SAMIJLeeds (West Yorkshire, UK) |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find next blank column with Macro
Hi Don
Boy your quick , thanks very much for you reply but it still keeps pasting into the same coulmn here is my full code, I may have something wrong in the code thanks again for your help Kind regards Samijleeds "Don Guillett" wrote: you might like this better LastCol = Sheets("Finds").cells(1,columns.count).End(xlToLef t).Column+1 -- Don Guillett Microsoft MVP Excel SalesAid Software "samijleeds" wrote in message ... Hi Guys I have a question you may find a bit daft but it has me stumped. I have a macro that finds the data I am looking for and pastes it into a new worksheet ok but what I would like it to do is look along the columns and find the next blank column and paste the data there so that with each paste the macro will paste the new data into the next blank available column. so first past will be in column A, then next paste in column B then next in C and so on I have this code at the moment but all it does is find the same column and over write the data that is already there Dim LastCol As Long LastCol = Sheets("Finds").Range("IV1").End(xlToLeft).Offset( 0, 1).Column Ive searched through all the posts and have found it very very helpful for other things but cant find anything on this Thanks for you help in advance SAMIJLeeds (West Yorkshire, UK) |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find next blank column with Macro
Hi All you very helpful guys
thanks for the quick posts and replies but it still keeps pasting over the same column not sure if it the rest of the code doing it now but here it is thanks again for all ytour help kind regards samijleeds <code Sub yes() 'Dim variables Dim y As Long Dim starta As String Dim tr As Long, tc As Long Dim sr As Long, sc As Long Dim s As Worksheet, t As Worksheet Dim n As Range Dim strToFind As String Dim flstc As Range Dim LastCol As Long 'Setup Application.ScreenUpdating = False LastCol = Sheets("Finds").Cells(1, Columns.Count).End(xlToLeft).Column Set t = Sheets("Finds") Set s = Sheets("Draws") strToFind = InputBox("What's The Number to Search for?") 'Get last used row in Target Sheet t.Select tr = Range("d65536").End(xlUp).Offset(1, 0).Row tc = LastCol 'Start in search sheet at C4 s.Select Range("c65536:j65536").Select 'Get 'yes' y = Cells.Find(What:=strToFind, After:=Activecell, LookIn:=xlFormulas, LookAt:= _ xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _ False).Activate 'Save start address for comparison starta = Activecell.Address 'Label for goto (pseudo loop) nextyes: 'Get row/col for copy paste sr = Activecell.Row sc = Activecell.Column 'Copy Column heading value to target sheet/cell in first column 'Cells(1, sc).Copy Destination:=t.Cells(tr, tc) 'Copy column "A" cell value to target sheet/cell Cells(sr, 1).Copy Destination:=t.Cells(tr, tc + 1) 'Set destination = plus one row tr = tr + 1 'Find next yes (til done) y = Cells.FindNext(After:=Activecell).Activate If Activecell.Address = starta Then t.Select Exit Sub End If 'pseudo loop GoTo nextyes End Sub <Code/ "Chip Pearson" wrote: Try some code like the following: Sub AAA() Dim WS As Worksheet Dim WhatRow As Long Dim FirstUnusedColumnNum As Long Dim FirstUnusedColumnRng As Range Set WS = Worksheets("Sheet1") '<<<< CHANGE AS DESIRED WhatRow = 1 '<<<< CHANGE AS DESIRED With WS Set FirstUnusedColumnRng = .Cells(WhatRow, .Columns.Count).End(xlToLeft) If FirstUnusedColumnRng.Column < 1 Then Set FirstUnusedColumnRng = FirstUnusedColumnRng(1, 2) End If FirstUnusedColumnNum = FirstUnusedColumnRng.Column End With Debug.Print "First used range: " & FirstUnusedColumnRng.Address, _ "First unused column number: " & FirstUnusedColumnNum End Sub -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting www.cpearson.com (email on the web site) "samijleeds" wrote in message ... Hi Guys I have a question you may find a bit daft but it has me stumped. I have a macro that finds the data I am looking for and pastes it into a new worksheet ok but what I would like it to do is look along the columns and find the next blank column and paste the data there so that with each paste the macro will paste the new data into the next blank available column. so first past will be in column A, then next paste in column B then next in C and so on I have this code at the moment but all it does is find the same column and over write the data that is already there Dim LastCol As Long LastCol = Sheets("Finds").Range("IV1").End(xlToLeft).Offset( 0, 1).Column Ive searched through all the posts and have found it very very helpful for other things but cant find anything on this Thanks for you help in advance SAMIJLeeds (West Yorkshire, UK) |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find next blank column with Macro
Not IF you use
+1 -- Don Guillett Microsoft MVP Excel SalesAid Software "samijleeds" wrote in message ... Hi Don Boy your quick , thanks very much for you reply but it still keeps pasting into the same coulmn here is my full code, I may have something wrong in the code thanks again for your help Kind regards Samijleeds "Don Guillett" wrote: you might like this better LastCol = Sheets("Finds").cells(1,columns.count).End(xlToLef t).Column+1 -- Don Guillett Microsoft MVP Excel SalesAid Software "samijleeds" wrote in message ... Hi Guys I have a question you may find a bit daft but it has me stumped. I have a macro that finds the data I am looking for and pastes it into a new worksheet ok but what I would like it to do is look along the columns and find the next blank column and paste the data there so that with each paste the macro will paste the new data into the next blank available column. so first past will be in column A, then next paste in column B then next in C and so on I have this code at the moment but all it does is find the same column and over write the data that is already there Dim LastCol As Long LastCol = Sheets("Finds").Range("IV1").End(xlToLeft).Offset( 0, 1).Column Ive searched through all the posts and have found it very very helpful for other things but cant find anything on this Thanks for you help in advance SAMIJLeeds (West Yorkshire, UK) |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find next blank column with Macro
Hi Don
It still doesnt work, Ive tried putting different numbers at the end to see it will paste into any column but it still just pastes into the same column Thanks for your reply Kind regards Samijleeds "Chip Pearson" wrote: Try some code like the following: Sub AAA() Dim WS As Worksheet Dim WhatRow As Long Dim FirstUnusedColumnNum As Long Dim FirstUnusedColumnRng As Range Set WS = Worksheets("Sheet1") '<<<< CHANGE AS DESIRED WhatRow = 1 '<<<< CHANGE AS DESIRED With WS Set FirstUnusedColumnRng = .Cells(WhatRow, .Columns.Count).End(xlToLeft) If FirstUnusedColumnRng.Column < 1 Then Set FirstUnusedColumnRng = FirstUnusedColumnRng(1, 2) End If FirstUnusedColumnNum = FirstUnusedColumnRng.Column End With Debug.Print "First used range: " & FirstUnusedColumnRng.Address, _ "First unused column number: " & FirstUnusedColumnNum End Sub -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting www.cpearson.com (email on the web site) "samijleeds" wrote in message ... Hi Guys I have a question you may find a bit daft but it has me stumped. I have a macro that finds the data I am looking for and pastes it into a new worksheet ok but what I would like it to do is look along the columns and find the next blank column and paste the data there so that with each paste the macro will paste the new data into the next blank available column. so first past will be in column A, then next paste in column B then next in C and so on I have this code at the moment but all it does is find the same column and over write the data that is already there Dim LastCol As Long LastCol = Sheets("Finds").Range("IV1").End(xlToLeft).Offset( 0, 1).Column Ive searched through all the posts and have found it very very helpful for other things but cant find anything on this Thanks for you help in advance SAMIJLeeds (West Yorkshire, UK) |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find next blank column with Macro
Without going thru all of your code, you may like this idea better which
works from anywhere in the workbook withOUT any selections. It asks you what to look forlooks for it in column d of the source sheetfinds it and puts the value in the next available row on the destination sheet. Sub findandcopy() Set sc = Sheets("sheet2") Set ds = Sheets("sheet3") what = InputBox("Num to find") With sc With .Range("d2:d" & .Cells(Rows.Count, "c").End(xlUp).Row) Set c = .Find(what, LookIn:=xlValues, lookat:=xlWhole) If Not c Is Nothing Then firstAddress = c.Address Do dlr = ds.Cells(Rows.Count, "a").End(xlUp).Row + 1 ds.Cells(dlr, 1).Value = c.Value Set c = .FindNext(c) Loop While Not c Is Nothing And c.Address < firstAddress End If End With End With End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "samijleeds" wrote in message ... Hi All you very helpful guys thanks for the quick posts and replies but it still keeps pasting over the same column not sure if it the rest of the code doing it now but here it is thanks again for all ytour help kind regards samijleeds <code Sub yes() 'Dim variables Dim y As Long Dim starta As String Dim tr As Long, tc As Long Dim sr As Long, sc As Long Dim s As Worksheet, t As Worksheet Dim n As Range Dim strToFind As String Dim flstc As Range Dim LastCol As Long 'Setup Application.ScreenUpdating = False LastCol = Sheets("Finds").Cells(1, Columns.Count).End(xlToLeft).Column Set t = Sheets("Finds") Set s = Sheets("Draws") strToFind = InputBox("What's The Number to Search for?") 'Get last used row in Target Sheet t.Select tr = Range("d65536").End(xlUp).Offset(1, 0).Row tc = LastCol 'Start in search sheet at C4 s.Select Range("c65536:j65536").Select 'Get 'yes' y = Cells.Find(What:=strToFind, After:=Activecell, LookIn:=xlFormulas, LookAt:= _ xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _ False).Activate 'Save start address for comparison starta = Activecell.Address 'Label for goto (pseudo loop) nextyes: 'Get row/col for copy paste sr = Activecell.Row sc = Activecell.Column 'Copy Column heading value to target sheet/cell in first column 'Cells(1, sc).Copy Destination:=t.Cells(tr, tc) 'Copy column "A" cell value to target sheet/cell Cells(sr, 1).Copy Destination:=t.Cells(tr, tc + 1) 'Set destination = plus one row tr = tr + 1 'Find next yes (til done) y = Cells.FindNext(After:=Activecell).Activate If Activecell.Address = starta Then t.Select Exit Sub End If 'pseudo loop GoTo nextyes End Sub <Code/ "Chip Pearson" wrote: Try some code like the following: Sub AAA() Dim WS As Worksheet Dim WhatRow As Long Dim FirstUnusedColumnNum As Long Dim FirstUnusedColumnRng As Range Set WS = Worksheets("Sheet1") '<<<< CHANGE AS DESIRED WhatRow = 1 '<<<< CHANGE AS DESIRED With WS Set FirstUnusedColumnRng = .Cells(WhatRow, .Columns.Count).End(xlToLeft) If FirstUnusedColumnRng.Column < 1 Then Set FirstUnusedColumnRng = FirstUnusedColumnRng(1, 2) End If FirstUnusedColumnNum = FirstUnusedColumnRng.Column End With Debug.Print "First used range: " & FirstUnusedColumnRng.Address, _ "First unused column number: " & FirstUnusedColumnNum End Sub -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting www.cpearson.com (email on the web site) "samijleeds" wrote in message ... Hi Guys I have a question you may find a bit daft but it has me stumped. I have a macro that finds the data I am looking for and pastes it into a new worksheet ok but what I would like it to do is look along the columns and find the next blank column and paste the data there so that with each paste the macro will paste the new data into the next blank available column. so first past will be in column A, then next paste in column B then next in C and so on I have this code at the moment but all it does is find the same column and over write the data that is already there Dim LastCol As Long LastCol = Sheets("Finds").Range("IV1").End(xlToLeft).Offset( 0, 1).Column Ive searched through all the posts and have found it very very helpful for other things but cant find anything on this Thanks for you help in advance SAMIJLeeds (West Yorkshire, UK) |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find next blank column with Macro
Hi Guys
thanks you for all your help, I have managed to sort this out. My coding was not allowing the paste columns to be pasted, I had the last know column set as column D instead of IV once Id changed it it worked, thank you again your brill SamijLeeds "John Bundy" wrote: I use: lastCol = Sheets("Find").Cells(1, Columns.Count).End(xlToLeft).Column my first row always has data, hence the cells(1 portion, yours may not, this gets the last column, to paste in the next just put lastCol+1 -- -John Please rate when your question is answered to help us and others know what is helpful. "samijleeds" wrote: Hi Guys I have a question you may find a bit daft but it has me stumped. I have a macro that finds the data I am looking for and pastes it into a new worksheet ok but what I would like it to do is look along the columns and find the next blank column and paste the data there so that with each paste the macro will paste the new data into the next blank available column. so first past will be in column A, then next paste in column B then next in C and so on I have this code at the moment but all it does is find the same column and over write the data that is already there Dim LastCol As Long LastCol = Sheets("Finds").Range("IV1").End(xlToLeft).Offset( 0, 1).Column Ive searched through all the posts and have found it very very helpful for other things but cant find anything on this Thanks for you help in advance SAMIJLeeds (West Yorkshire, UK) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to find blank cell in column | New Users to Excel | |||
Trying to find the fourth blank cell in a column | Excel Discussion (Misc queries) | |||
Find First Non blank cell than find column header and return that value | Excel Worksheet Functions | |||
Find 1st blank cell in column & sum to the same row in another col | Excel Worksheet Functions | |||
Find first non-blank or non-zero in a column of data | Excel Discussion (Misc queries) |