Posts by Eggcel

Re: For Next Loop

Here is my go at your issue. This does not have the part where it would recognise if it wasn't able to make the two values equal, but it may be a start in the right direction. It does increase or decrease A1 one year at a time depending on the beginning relationship between the two. I didn't have time to add the last bit as I had my own code dilemma today.

[code]
Sub changedate()
Dim rng1 As Range, rng2 As Range
Dim date1 As String
Dim date2 As String
Dim mth As String
Dim dy As String
Dim i As Integer
recheck:
Set rng1 = Worksheets("Sheet1").Range("a1")
Set rng2 = Worksheets("Sheet1").Range("b1")
'check if a1 is date value
If IsDate(rng1) And IsDate(rng2) Then
'set variables for comparison and month and day of a1 for change in value
date1 = Year(rng1)
mth = Month(rng1)
dy = Day(rng1)
date2 = Year(rng2)
'compare a1&b1
Else: Exit Sub
End If
If date1 = date2 Then Exit Sub
If date1 > date2 Then
date1 = date1 - 1
Cells(1, 1) = DateSerial(date1, mth, dy)
GoTo recheck
Else
If date2 > date1 Then
date1 = date1 + 1
Cells(1, 1) = DateSerial(date1, mth, dy)
GoTo recheck ' start at top and redo until a1 and b1 year value are equal
End If
End If
End Sub

Re: Saving an .xls file from a webpage hyperlink to a specified folder and name.

I found this little bit :

dim p as string
p = IE.document.Links(9).toString

that when put in a loop helped me to identify which link number and put the name into a string so that I could save it to a file.

Re: VBA For Loop

It's just A1 and B1 that you are interested in or is it the entire columns A&B?

Re: VBA For Loop

Could you put a formula in A1 that would match the year number of the date in B1? How are these two interrelated?

Good day, All!

I am writing some vba code in excel that accesses a webpage, enters the appropriate values, makes the appropriate selections and then opens a second page. The fields on the second page are filled out and the submit button is selected. On the third page, once again I select and fireevent and then a graph comes up with a hyperlink beneath it, labeled "dowloadfile to Excel". My issue begins at this point. I have not been able to figure out how to select this hyperlink and to do so in a way that I can save the .xls as a specific file name in a specific folder. I can see the :http link in the source code for the webpage, but the last portion of this changes everytime this page comes up.

I had previously created a workbook which takes equipment downtime codes and sorts them with paretos, pivottables and charts. The data that is imported comes from the aforementioned webpages, but has to be manually obtained which can be a pain to keep up with. I am trying to automate this part of the process, and I actually thought the hardest part was done. I was able to figure out the navigation on the webpages and inputs by a keen use of the search engine and some trial and error. I could provide some of my existing code if it would help anyone to better understand my dilemna, but my assumption was that since it was after the working portion of the code that I was having the issue that it would be irrelevant. Also, I would be happy to try to better describe what I am trying to accomplish if what I have written here is not enough information. I am sure that I am like many of you, and my brain won't cease until I solve this puzzle. Thank you for your consideration and your time.

Re: VSB relative reference

On the toolbar with the stop recording button there should be a down arrow. Put your cursor over this and "toolbar options" should be displayed. Click this and "add remove buttons" will appear. Hover the cursor over this and it will expand further. Hover over the "stop recording" and another box will show up with the stop recording button and the relative reference. Click on the relative reference icon to add the button back to the toolbar.

Re: varying destination row with copy and paste on filtered data

I was going about it the wrong way. I had initially tried using the code to find the last row in the range on the destination sheet, but I kept getting errors. It was then that I decided to try to vary the destination row with the original row. Once I solved my initial issue though, it worked as intended. I used .activate for the sheets prior to the .copy and .paste and I stopped getting errors. Is there a better way to code this than doing this, possibly with the "With" command? The next issue that I ran across was getting the last few rows of data that did not fall into my step of 2500(used due to the limits on copy), but I was able to vary the variable value at the end so that it did end with the "lastrow" value. I am including my code to show what I am talking about. Any comments or critiques of how I could improve or modify things would be appreciated, as I am very much still learning the vastness that is vba.

Sub filtertodate()Dim DtoFilt As LongDim lastrow As Long Dim i As LongDim bRow As LongDim eRow As LongApplication.ScreenUpdating = FalseApplication.CutCopyMode = False'establish date to filterDtoFilt = CDate(Sheets("FOOD").Range("a3"))'clear destination sheetWorksheets("Sheet1").Range("A2:e" & Range("e" & Cells.Rows.Count).End(xlUp).Row).ClearContents'find last row on sheetWith Worksheets("BUDGET")lastrow = .Cells(.Rows.Count, "A").End(xlUp).RowEnd With'filter sheet to all items with date range after established dateWorksheets("BUDGET").Range("A1").AutoFilter Field:=1, Criteria1:=">" & DtoFilt  'determine if total rows is more than allowable copy/paste limitIf lastrow > 2500 Theni = 2500Elsei = lastrow End IfbRow = 2'loop by 2500 rows to endFor eRow = i To lastrow Step 2500Worksheets("BUDGET").ActivateWorksheets("BUDGET").Range("a" & bRow & ":e" & bRow, Range("A" & eRow & ":E" & eRow)).CopyWorksheets("Sheet1").Activate'find next blank cell of target columnWorksheets("Sheet1").Range("a" & Cells.Rows.Count).End(xlUp).SelectActiveCell.Offset(1, 0).PasteSpecial xlPasteValuesbRow = eRow + 1'when bRow surpasses the lastrow number exit the loopIf bRow > lastrow Then Exit For' since the total rows/2500 is likely not a whole number set the last item in loop to be equal to the step #If lastrow - bRow < 2500 Then eRow = lastrow - 2500Next eRow Application.CutCopyMode = False'clear the filter on sheetWorksheets("BUDGET").ShowAllDataApplication.ScreenUpdating = TrueEnd Sub

I have been working on a macro that filters the data on one sheet based on a date variable and then copies this data to another sheet. I originally had set this up and it was working fine, but the complications arose when the source data got to be too large. I added a loop, but I have had issue with the destination sheet range as once the rows are filtered there are less rows on the destination sheet than on the original sheet. I am trying to figure out how I track the row of the destination sheet so that as I paste each 2500 rows they are in the next available rows. I am including my existing macro. With this macro I have gaps on the destination sheet where the items on the original sheet have been omitted due to filter.

Sub filtertodate()Dim DtoFilt As LongDim rw1last As Long, rw2last As LongDim i As LongDim g As LongDim h As LongDim j As LongApplication.CutCopyMode = FalseDtoFilt = CDate(Sheets("FOOD").Range("a3"))With Worksheets("BUDGET")rw1last = .Cells(.Rows.Count, "A").End(xlUp).RowEnd WithWith Worksheets("Sheet1")rw2last = .Cells(.Rows.Count, "A").End(xlUp).RowEnd WithWorksheets("BUDGET").Range("A1").AutoFilter Field:=1, Criteria1:=">" & DtoFiltIf rw1last > 2500 Theni = 2500g = 2Elsei = rw1lastg = 2 End Ifj = 2For h = i To rw1last Step 2500Worksheets("BUDGET").Range("a" & g & ":e" & g, Range("A" & h & ":E" & h)).CopyWorksheets("Sheet1").Range("A" & g).PasteSpecial xlPasteValuesg = h + 1Next hApplication.CutCopyMode = FalseWorksheets("BUDGET").AutoFilterMode = FalseEnd Sub

Thanks in advance for any assistance on this. I could not have written this macro to begin with ,without the knowledge that I have gained from those that frequent these boards.

Re: Comparing values in two sheets in the same workbook in different columns

Through further troubleshooting, I was able to determine the problem I was having. I had specific names for the sheets that were used as 'sheet1' and 'sheet2'. The name for 'sheet2' had a space in it, which I guess does not work well in vb. I will look up more information as to the particulars on this. As I had stated in my previous post, I moved the two columns of data to a new workbook and it worked fine. The problem came when I changed the worksheet names to what they were in the original workbook. Thanks again for your assistance. I hope to improve to the point where I can be of help to someone, someday also.

Re: Comparing values in two sheets in the same workbook in different columns

I figured the putting the 'on error resume next' wasn't a fix for the issue, but I was hoping it would allow me to possibly see what was causing it. My novice skills are apparent, I know. I copied the macro to a new workbook as well as the two columns of data, and it ran fine. I will try to determine the issue with the original workbook and what is creating the issue I am having there. I appreciate your time and assistance.

Re: Comparing values in two sheets in the same workbook in different columns

Thank you for your response.I am having a couple of complications with this .The first issue I had was when there was nothing in a1:b1 on sheet3. I have added labels to these columns and that is ok now.Next, I am getting a 'run-time error 13' 'type mismatch' at

If Evaluate("COUNTIF(" & strSourceTabTwo & "!" & rngDataSetTwo.Address & "," & strSourceTabOne & "!" & rngCell.Address & ")") = 0 Then

I put

on error resume next

before this line and the error no longer comes up, but I am not sure if this was the solution I needed.Lastly, after the macro runs ,all of the values are listed in the differences(B) column. I even went as far as copying the values from column e on sheet2 to column c on sheet1 and the values still show up as differences. I am searching through the macro to try to identify why this is happening, but would appreciate any assistance. Just reading through the code is helping me to better understand macros and vba.Thank you.

I am relatively green to the excel macro, but have learned alot as of late. I am trying to write a macro that compares the values in two columns on two separate worksheets in the same workbook and list the differences and similarites in separate columns on a third sheet. The values are in the "C" column on one sheet and the "E" column on the other. I have seen a few examples on other sites and forums, but they do not meet my specific needs. I have tried to modify them to my specific purpose, with limited success. The values in the two columns are numbers. Any assistance or a nudge in the right direction would be appreciated. I will specify further if the information I have provided is too vague.compare each row value in worksheet1 column c to each row value worksheet2 column e list matches on sheet3 column a and differences in column b