Help with VBA macro

Allison1995

New Member
Joined
Oct 12, 2018
Messages
6
Hello everyone!

First of all I have to say is a great forum! found really interesting stuff that helped me with my VBA skills.

Secondly, I was wondering if someone could help me with a simple macro that I couldn't find online and is driving me crazy.

What I have is a table with different times on the first column starting at A5 :e.g 0.01hr, 0.023 hr, 0.011 hr, 0.18 hr, 0.205 hr etc. and different values in the rest.

This is really big matrix that goes up to 180 hours, what I'm trying to do is ;

-Look at the times from A5 to the last number of the column.
-Choose a time interval e.g. every 10 min.
-Select the row that are closer to that time interval (this will be, close to 0.1, 0.2 0.3hr .....). This will have to compare the previous and next number between 10, for example 9.1 and 10.2 . Whatever number is closer to 10, is the row I want to select.
-Move to the next interval (0.2hr min) and do the same until it has finish to the last interval (e.g. 200hr)
-Copy that rows closes to my time intervals and paste them into Sheet4.

I don't think I'm doing this the easy way. What I try is creating a column with my times that I want to select. e.g. 10, 20, 30, 40 .... ("D"). Then used the Match function to select the row with the time closes to the one I set, and them used INDEX, to see the value, copy it and paste it.

Sub CreateSheet()
'this creates the new sheet4 where the data will be pasted.

With ThisWorkbook
.Sheets.Add(After:=.Sheets(.Sheets.Count)).Name = "Sheet4"
End With

Worksheets("Sheet2").Range("G6:G7000").Formula = "=(=MATCH(MIN(ABS(A$1:A$36-D4)),ABS(A$1:A$36-D4),0))"

Worksheets("Sheet2").Range("G6:G7000").Formula = "=(=INDEX(B$1:C$36,MATCH(MIN(ABS(A$1:A$36-D1)),ABS(A$1:A$36-D1),0),0))"
Worksheets("Sheet2").Range("G6:G7000").Formula = "=(=INDEX(B$1:C$36,MATCH(MIN(ABS(A$1:A$36-D1)),ABS(A$1:A$36-D1),0),0))"

Sheets("Sheet2").Rows("5").EntireRow.Copy
Sheets("Sheet4").Rows("2").PasteSpecial xlPasteValues

End Sub

Will be great if anyone could give me a hand or give me some guidance .

Thank you in advance, much appreciated it

(P.S. I'm new to the forum and couldn't find it anywhere)
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
Allison1995,

Welcome to the Board.

It would help quite a bit if you could post sample data. (Please see the link in my signature on how to post Excel data.)

Cheers,

tonyyy
 
Upvote 0
Welcome to the Mrexcel board!

I'm not quite sure exactly what your column A data looks like but see if this gets you going in the right direction.
For this sample data, and looking for multiples of 10 (see 'Const' line in the code below), the code copies the green rows to Sheet4.
I have assumed
- this data sheet is the active sheet when the code is run
- there is a header row in row 4 that can be used to determine the extent of columns of data
- column A is sorted smallest to largest

Post back with more details and sample data as requested by Tony if this is not what you need.


Excel 2016
ABCD
4Hdr 1Hdr 2Hdr 3Hdr 4
51.317597
64.274642
75.6562667
88.2716274
99.3509596
109.9434330
1111.334304
1213.52088100
1314.592837
1416.6809294
1518.8171072
1618.9931367
1719.25690100
1819.452449
1919.8867886
2021.517544
2121.7559649
2222.7519721
2323.5926920
2424.398682
2525.2898134
2626.5917451
2727.3488343
2828.8376458
2930.4594682
3030.7788976
3133.190257
3233.5471007
333573747
3435.547747
3536621079
3637.6511160
3738.3444511
3838.735768
3939222455
4039.173476
4142.4614168
Sheet1


Code:
Sub Sample_Rows()
  Dim a As Variant, b As Variant
  Dim i As Long, nc As Long
  Dim dTest As Double
  
  Const Interval As Double = 10
  
  With Range("A4", Range("A" & Rows.Count).End(xlUp).Offset(1)).Resize(, Cells(4, Columns.Count).End(xlToLeft).Column)
    nc = .Columns.Count + 1
    a = .Columns(1).Value
    a(1, 1) = a(2, 1)
    ReDim b(1 To UBound(a), 1 To 1)
    b(1, 1) = 1
    dTest = Interval
    For i = 2 To UBound(a)
      If Int(a(i, 1) / dTest) > Int(a(i - 1, 1) / dTest) Then
        b(IIf(Abs(a(i, 1) - dTest) > Abs(a(i - 1, 1) - dTest), i - 1, i), 1) = 1
        dTest = dTest + Interval
      End If
    Next i
    .Columns(nc).Value = b
    Intersect(.EntireColumn, .Columns(nc).SpecialCells(xlConstants).EntireRow).Copy Destination:=Sheets("Sheet4").Range("A1")
    .Columns(nc).ClearContents
  End With
End Sub
 
Upvote 0
Hello Peter!

Thank you very much, this is exactly what I needed, you are a champion!.

I did get it to work with Matlab but was struggling with VBA. One last questions, will it be possible to also record and paste the first value ? (this is the one close to 0)

Much appreciated it!.

Allison
 
Upvote 0
Hi Peter,

Please see attached the overall code. I'm getting and error Run-time error '13': Type mismatch. Apparently is because I have another variable with the same name but can't find it. If I ran just your code without anything else, works perfectly.

Code:
Sub CreateSheet()
'create the different sheets I will work with
    With ThisWorkbook
        .Sheets.Add(After:=.Sheets(.Sheets.Count)).Name = "Sheet2"
        .Sheets.Add(After:=.Sheets(.Sheets.Count)).Name = "Sheet3"
        .Sheets.Add(After:=.Sheets(.Sheets.Count)).Name = "Sheet4"
    End With
    
 'loads the data from the sheets, and close the workbooks
 'first workbook
    
Workbooks.Open Filename:="C:\Users\admin\Desktop\Excel\1.xlsx"
ActiveSheet.Name = "1"

Workbooks("1").Sheets(1).UsedRange.Copy Destination:=Workbooks("ThisWorkbook").Sheets(2).Range("A1")

ActiveWorkbook.Close False
 
Workbooks.Open Filename:="C:\Users\admin\Desktop\Excel\2.xlsx"
ActiveSheet.Name = "1"

'second workbook
Workbooks("2").Sheets(1).UsedRange.Copy Destination:=Workbooks("ThisWorkbook").Sheets(3).Range("A1")
ActiveWorkbook.Close False

'Operates woksheets 3
Worksheets("Sheet3").Range("B2").Delete
Worksheets("Sheet3").Range("O2").Formula = "=((SUM('Sheet3'!B:B))/60000)"
Worksheets("Sheet3").Range("P2").Formula = "=((SUM('Sheet3'!F:F))/60000)"
Sheets("Sheet3").Range("O2").Copy
Sheets("Sheet1").Range("D2").PasteSpecial xlPasteValues

'Operates woksheets 2
Sheets("Sheet2").Range("G1").EntireColumn.Insert
Worksheets("Sheet2").Range("G5").Formula = "=F5"
Worksheets("Sheet2").Range("G6:G7000").Formula = "=(G5+F6)"

'Looks for the values closes to 0.1 and pastes them into sheet 4

  Dim a As Variant, b As Variant
  Dim i As Long, nc As Long
  Dim dTest As Double
  
  Const Interval As Double = 0.1
  
  With Range("A4", Range("A" & Rows.Count).End(xlUp).Offset(1)).Resize(, Cells(4, Columns.Count).End(xlToLeft).Column)
    nc = .Columns.Count + 1
    a = .Columns(1).Value
    a(1, 1) = a(2, 1)
    ReDim b(1 To UBound(a), 1 To 1)
    b(1, 1) = 1
    dTest = Interval
    For i = 2 To UBound(a)
      If Int(a(i, 1) / dTest) > Int(a(i - 1, 1) / dTest) Then
        b(IIf(Abs(a(i, 1) - dTest) > Abs(a(i - 1, 1) - dTest), i - 1, i), 1) = 1
        dTest = dTest + Interval
      End If
    Next i
    .Columns(nc).Value = b
    Intersect(.EntireColumn, .Columns(nc).SpecialCells(xlConstants).EntireRow).Copy Destination:=Sheets("Sheet4").Range("A1")
    .Columns(nc).ClearContents
  End With



End Sub


Thank you in advance.

Allison
 
Upvote 0
Hello Peter!

Thank you very much, this is exactly what I needed,
OK, great.


will it be possible to also record and paste the first value ? (this is the one close to 0)
So, do you just mean always paste the first row after the headings? That is row 5?



Please see attached the overall code. I'm getting and error Run-time error '13': Type mismatch. Apparently is because I have another variable with the same name but can't find it.
What line gives the error (click Debug when it happens) and what makes you think the problem is a duplicate variable name?

My guess is that the problem line is this one.
Rich (BB code):
If Int(a(i, 1) / dTest) > Int(a(i - 1, 1) / dTest) Then
If so, when you get the error & click Debug, hover directly over each of the highlighted 'a's and see what value is recorded. I'm guessing a text value.
It might give you a clue as to what is going wrong. Could be that the code is operating on the wrong sheet? Remember that the code is designed to run on the active sheet and I'm not sure what that is for you at the time the code gets to this section as I am unable to test due to not having the required other workbooks etc.
 
Upvote 0
Hi Peter,

Regarding your questions;

1-So, do you just mean always paste the first row after the headings? That is row 5? Yes, atm the macro starts with the first time step (e.g. 10min), will it be possible to consider the first value once it paste it into "sheet4''?

2-What line gives the error (click Debug when it happens) and what makes you think the problem is a duplicate variable name? Apparently fails once it reaches the Next i . I mentioned the duplicate variable name because I google it and that was one of the possible solution (not 100% sure if that is correct)

3-Could be that the code is operating on the wrong sheet? I added a line with Worksheets("Sheet2"). Activate just to make sure is the right one but stills does not work.

Is interesting to see that if I run the full macro (yours and the one I added that creates the different sheets) it gives me the error. However, if I run the full macro, I stop it once it has created "Sheet2", create a new workbook and copy the "Sheet2", then paste your macro, it works.

Is it possible to upload excels files into the post ? .

Thanks again for your patience.
Allison
 
Upvote 0
Is it possible to upload excels files into the post ? .
No, but you could upload simple sample file(s) to a public file-share site (eg Dropbox) and provide share-link(s) in your post.

In the code you posted there is no reason to believe that the variable i is duplicated, and 'Next i' would be a very unusual place for the code to error!
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,245
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top