Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VAB to copy cell values into new Sheet, Overwrite if needed and based off of Cell Value in a column
I have two sheets. Sheet1 and Sheet2
On sheet1 I have some finaical numbers that run. For instance I want to run a particulay day of data and it returns certain values. I would like to be able to click a command button and have the data copied to sheet2 into certain cells in a column based of the day of the week. For instance if B2 on Sheet 1 Equals Monday I want it to find the column on sheet2 that has Monday in it and copy certain cells from sheet1 into certain cells in sheet two under that column. Thanks, David |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VAB to copy cell values into new Sheet, Overwrite if needed and ba
Based strictly on your description, this would be a start.
set rng = Worksheets("Sheet2").Cells.Find( _ worksheets("Sheet1").Range("b2")) set rng1 = Worksheets("Sheet2").Range("CertainCells").EntireR ow set rng2 = Intersect(rng1,rng.EntireColumn) j = 0 for each cell in Worsheets("Sheet1").Range("Data") j = j + 1 i = 0 for each cell1 in rng2 i = i + 1 if i = j then cell1 = cell exit for end if Next cell1 Next cell -- Regards, Tom Ogilvy "gumby" wrote: I have two sheets. Sheet1 and Sheet2 On sheet1 I have some finaical numbers that run. For instance I want to run a particulay day of data and it returns certain values. I would like to be able to click a command button and have the data copied to sheet2 into certain cells in a column based of the day of the week. For instance if B2 on Sheet 1 Equals Monday I want it to find the column on sheet2 that has Monday in it and copy certain cells from sheet1 into certain cells in sheet two under that column. Thanks, David |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VAB to copy cell values into new Sheet, Overwrite if needed and ba
Debug this. The premise is correct. Just insert a value in B2 and put the
string 'Monday' somewhere in the 10x10 block in sheet 2 and the program will search for it in sheet 2 and place the B2 contents right below it. Private Sub CommandButton1_Click() Dim Monday As Double Dim i, j As Integer Monday = Worksheets(1).Cells(2, 1).Value For i = 1 To 10 For j = 1 To 10 If Worksheets(2).Cells(i, j).Value = "Monday" Then Worksheets(2).Cells(i, (j + 1)).Value = Monday End If Next Next End Sub "gumby" wrote: I have two sheets. Sheet1 and Sheet2 On sheet1 I have some finaical numbers that run. For instance I want to run a particulay day of data and it returns certain values. I would like to be able to click a command button and have the data copied to sheet2 into certain cells in a column based of the day of the week. For instance if B2 on Sheet 1 Equals Monday I want it to find the column on sheet2 that has Monday in it and copy certain cells from sheet1 into certain cells in sheet two under that column. Thanks, David |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
VAB to copy cell values into new Sheet, Overwrite if needed and ba
On Jul 13, 1:32 pm, Philosophaie
wrote: Debug this. The premise is correct. Just insert a value in B2 and put the string 'Monday' somewhere in the 10x10 block in sheet 2 and the program will search for it in sheet 2 and place the B2 contents right below it. Private Sub CommandButton1_Click() Dim Monday As Double Dim i, j As Integer Monday = Worksheets(1).Cells(2, 1).Value For i = 1 To 10 For j = 1 To 10 If Worksheets(2).Cells(i, j).Value = "Monday" Then Worksheets(2).Cells(i, (j + 1)).Value = Monday End If Next Next End Sub "gumby" wrote: I have two sheets. Sheet1 and Sheet2 On sheet1 I have some finaical numbers that run. For instance I want to run a particulay day of data and it returns certain values. I would like to be able to click a command button and have the data copied to sheet2 into certain cells in a column based of the day of the week. For instance if B2 on Sheet 1 Equals Monday I want it to find the column on sheet2 that has Monday in it and copy certain cells from sheet1 into certain cells in sheet two under that column. Thanks, David- Hide quoted text - - Show quoted text - It places 0 to the right of it. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
VAB to copy cell values into new Sheet, Overwrite if needed and ba
On Jul 13, 1:06 pm, Tom Ogilvy
wrote: Based strictly on your description, this would be a start. set rng = Worksheets("Sheet2").Cells.Find( _ worksheets("Sheet1").Range("b2")) set rng1 = Worksheets("Sheet2").Range("CertainCells").EntireR ow set rng2 = Intersect(rng1,rng.EntireColumn) j = 0 for each cell in Worsheets("Sheet1").Range("Data") j = j + 1 i = 0 for each cell1 in rng2 i = i + 1 if i = j then cell1 = cell exit for end if Next cell1 Next cell -- Regards, Tom Ogilvy "gumby" wrote: I have two sheets. Sheet1 and Sheet2 On sheet1 I have some finaical numbers that run. For instance I want to run a particulay day of data and it returns certain values. I would like to be able to click a command button and have the data copied to sheet2 into certain cells in a column based of the day of the week. For instance if B2 on Sheet 1 Equals Monday I want it to find the column on sheet2 that has Monday in it and copy certain cells from sheet1 into certain cells in sheet two under that column. Thanks, David- Hide quoted text - - Show quoted text - It appears to run, I get nor errors, but it does not work. The cells are not copied. Private Sub CommandButton1_Click() Set rng = Worksheets("1hr_Interval_Totals").Cells.Find( _ Worksheets("1hr_Intervals").Range("D59")) Set rng1 = Worksheets("1hr_Interval_Totals").Range("C5:O5").E ntireRow Set rng2 = Intersect(rng1, rng.EntireColumn) j = 0 For Each cell In Worksheets("1hr_Intervals").Range("D52:D58") j = j + 1 i = 0 For Each cell1 In rng2 i = i + 1 If i = j Then cell1 = cell Exit For End If Next cell1 Next cell End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
copy rows from one Data sheet to another sheet based on cell conte | Excel Discussion (Misc queries) | |||
Copy last cell with data in column E on one sheet to cell on anoth | Excel Worksheet Functions | |||
Copy values from a cell based on values of another cell | Excel Discussion (Misc queries) | |||
based on Cell/Column content ,cut one sheet's values and paste it in other sheet? | Excel Programming | |||
based on Cell/Column content ,cut one sheet's values and paste it in other sheet? | Excel Programming |