Posts by rsaha

Well I was able to come up with answer, for those who might find it useful, here is my solution:

Sub scrape_data1()


 Dim i As Long
    With Application
        .ScreenUpdating = False
        .Calculation = xlCalculationManual
    End With


        For i = 3 To 54
            If Cells(i, 4) = Cells(1, 8) Then
               Range(Cells(i, 1), Cells(i, 4)).Copy
               Range("P32").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
            End If
        Next
End Sub

here is my first attempt at creating a code for my problem:

Sub scrape_data1()


 Dim i As Date
    With Application
        .ScreenUpdating = False
        .Calculation = xlCalculationManual
    End With


        Sheet1.Select


        For i = 3 To 54
            If Range(Cells(i, 4)) = Range(Cells(1, 8)) Then
               Range(Cells(i, 1), Cells(i, 4)).Copy
               Range(Cells(i, 16)).PasteSpecial xlPasteValues
            End If
        Next
End Sub

however i keep getting the following error message:

[ATTACH=JSON]{"data-align":"none","data-size":"full","title":"vba error.PNG","data-attachmentid":1196872}[/ATTACH]

I also feel like the copy and paste portion of the code wont work either, but havent been able to get to that section yet. i would like to be able to loop through data and paste each hi after one another or each lo after each other in their respective sections.

I look forward to any and all solutions.

Hello all.

i have a set of data in grid form. The data provides me with hi, lo and a date for each hi and lo. in cell h1 i have a specific date. I would like to create a macro that goes through the grid and and if the hi/lo date matches the date in cell h1, it would take the data and place it in a separate section, hi data in section 'p-s' and lo data in section 'u-x'. I have attached a spreadsheet to serve as template as to what i am looking for. Thanks for all your help.

I was wondering if anyone had a gabillon pricing model in excel that they are willing to share. Thank you.

Re: Loop through a range of data and delete the top row and then copy to another shee

thank you very much this is great. it works perfectly.

Re: Loop through a range of data and delete the top row and then copy to another shee

gotcha. thanks for all your help thus far. i have been able to use what you written thus far and started implementing facets of it to my main workbook.

Re: Loop through a range of data and delete the top row and then copy to another shee

thanks bryce, i ran the file with new changes, however it errors out when there is only one line left and enter debug mode. it highlights the following line:

wsHistory.Range("B" & wsHistory.Range("B" & Rows.Count).End(xlUp).Offset(1).Row) = Application.WorksheetFunction.Correl(wsData.Range("B5:B15"), wsData.Range("C5:C15"))

Re: Loop through a range of data and delete the top row and then copy to another shee

I am fine with having error values for the last few rows of analysis.

Re: Loop through a range of data and delete the top row and then copy to another shee

Thanks. I just ran it. And it deletes the worksheet "data" and nothing gets copied over to worksheet "historical analysis".

Re: Loop through a range of data and delete the top row and then copy to another shee

Thanks for responding, it should stay at its current length. Thanks for pointing that out. I guess i could change the correlation function to include an offset function.

I have set a data on which i am running historical regression and correlation analysis on. I would like to create a macro that would copy my results to another worksheet, then delete the top row of data, run the analysis over again for the remaining data and repeat. i have attached a spreadsheet to help clarify what i am trying to do.

The data tab contains the data and analysis i am performing. In this case, I would like to copy cell b1 to worksheet "historical analysis" where it would be placed in column B, next to its corresponding date, e.g. cell B5. Then i would go ahead and delete the data in worksheet "data", row "5", with everything moving up one row, and the copy the analysis from B1 and paste in worksheet "historical analysis", cell "B6", so on and so forth.

The file i have provided is a small microcosm of what i am trying to do. Here is what i was able to come up with, though it doesnt work:

Sub delete_loop()




 Dim LastRow As Long
 
 For i = 17 To LastRow
    LastRow = Cells(Rows.Count, "A").End(xlUp).Row
    Sheets("data").Range("B1").Copy
    Sheets("historical analysis").Range("B40000").End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
    Rows("5:5").Delete Shift:=xlUp
    Application.CutCopyMode = False
 Next




End Sub

forum.ozgrid.com/index.php?attachment/71559/

Re: Loop through worksheets and copy last row down one row

Thank you very much. This great. I wasnt aware of the goto command. I have definitely learned quite a bit. Thank you again for all your help.

Re: Loop through worksheets and copy last row down one row

Actually the code works even with gaps. i made a couple of changes and everything seems to work expect one line of code that i added. I am trying select the first cell in the new range that i copied down, however i am getting an error message. here is the code that i am using:

Sub CopyNewRowToEnd_nonUniformSheets_pasteSpecial()
    Dim wsSheet As Worksheet
    Dim lTR As Long, lBR As Long, lLC As Long, lRC As Long
     
    For Each wsSheet In ThisWorkbook.Worksheets
        With wsSheet
             'this is just a demonstration of one way to get the co-ordinates of the cells on the sheet
             'in theory UsedRange could be used on it's own but it does not always return what is expected
             'all of this assumes there are no gaps in the range of used cells on the sheet
            lTR = .UsedRange.Row
            lLC = .UsedRange.Column
            lBR = .Cells(.Rows.Count, lLC).End(xlUp).Row
            lRC = .Cells(lTR, .Columns.Count).End(xlToLeft).Column
             
             'now we have the co-ordinates of the actual used range on the sheet we can copy a new row of formulas
            .Range(.Cells(lBR, lLC), .Cells(lBR, lRC)).Copy
            .Range(.Cells(lBR + 1, lLC), .Cells(lBR + 1, lRC)).PasteSpecial Paste:=xlFormulas
            .Range(.Cells(lBR + 1, lLC), .Cells(lBR + 1, lLC)).Select
        End With
    Next
End Sub

this is the line that is erroring out:

.Range(.Cells(lBR + 1, lLC), .Cells(lBR + 1, lLC)).Select

I appreciate the help.

Re: Loop through worksheets and copy last row down one row

Had one more question for you. How difficult would it be to amend the code to account for gaps in the data range?

Re: Loop through worksheets and copy last row down one row

Thank you very much. It works great. I really appreciate all the help.

Re: Loop through worksheets and copy last row down one row

I got this code to work with worksheets that are uniform, ie start in the same cell (A1). I am now looking for way to make it more flexible.

Sub loop_through_all_worksheets()




Dim ws As Worksheet
Dim starting_ws As Worksheet
Set starting_ws = ActiveSheet 'remember which worksheet is active in the beginning




For Each ws In ThisWorkbook.Worksheets
   ws.Activate
        Range("A1").Select
        Selection.End(xlDown).Select
        Range(Selection, Selection.End(xlToRight)).Select
        Selection.Copy
        Selection.Offset(1, 0).Select
        Selection.PasteSpecial Paste:=xlPasteFormulas
        Range("A1").Select
        Application.CutCopyMode = False
Next




starting_ws.Activate 'activate the worksheet that was originally active




End Sub

Hello everyone. I have a workbook with 3 worksheets, i would like a macro what would loop each worksheet, find the last row with formulas and copy it down to the next row. Unfortunately, the three tabs are not uniform. I have attached a spreadsheet example to better clarify what i am trying to do. I have highlighted the row where i would like to the formulas copied to. I was using the following code, which worked great for just one worksheet, but wasnt dynamic or flexible enough when i wanted to loop through several worksheets with data in different locations:

Sub appendToEnd()


    Dim myLastCell As Range
    Set myLastCell = LastCell(Worksheets("Sheet1").Range("A:W"))
    Dim lastCellCoords As String: lastCellCoords = "A" & myLastCell.Row & ":W" & myLastCell.Row


    Dim firstEmptyRow As Integer: firstEmptyRow = myLastCell.Row + 1
    Dim firstEmptyCoords As String: firstEmptyCoords = "A" & firstEmptyRow & ":W" & firstEmptyRow


    If Not myLastCell Is Nothing Then
        ' Now Copy the range:
        Worksheets("Sheet1").Range(lastCellCoords).Copy
        ' And paste to first empty row
        Worksheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial xlPasteFormulas
        Application.CutCopyMode = False
    Else
        MsgBox ("There is no data in specified range")
    End If


End Sub




Function LastCell(r As Range) As Range
'
' Note "&" denotes a long value; "%" denotes an integer value


    Dim LastRow&, lastCol%
    On Error Resume Next
    With r


  ' Find the last real row
    LastRow& = .Cells.Find(What:="*", _
      SearchDirection:=xlPrevious, _
      SearchOrder:=xlByRows).Row
      
  ' Find the last real column
    lastCol% = .Cells.Find(What:="*", _
      SearchDirection:=xlPrevious, _
      SearchOrder:=xlByColumns).Column
    End With


' Finally, initialize a Range object variable for
' the last populated row.
    Set LastCell = r.Cells(LastRow&, lastCol%)


End Function

I appreciate any and all help.

Re: Associating a date with specific group of numbers

thank you very much. it works perfectly.

I am currently working on a project that associates 6 cycle weeks with a particular rate month, e.g. cycles 1-6 correspond to jan-17 rate month, cylces 7-12 correspond to feb-17 rate month, etc. There are a total of 72 cycles per year that are associated with 12 months per year. Currently i have manually associated each rate month with its respective cycle week. I was looking for a way to use a formula that would make it more dynamic.

I have attached a spreadsheet with the data that i am working with. I am looking for the formula to be place in column B. I also have a section that associates the rate month and the last cycle in each rate month in columns D & E.

Thank you for all your help.

forum.ozgrid.com/index.php?attachment/71083/

Re: Creating Charts using a list box

I am sorry for the confusion. I am looking to for single list that allows multi-select, hence why iw as asking for a help with a list box, which allows you to do so.

I agree with you that i dont need 144 drop down boxes. My point was with the method you suggested would require me to have 144 drop boxes, which isnt feasible.