Posts by NewbieVBA

NewbieVBA · Jun 4th 2016

Re: Combine two Macro Codes together

@ SO
I took my syntax and placed into a loop like this:

Code

last_r2 = Sheets("sheet3").Range("A3").End(xlDown).Row ' this is the column with the different dates
last_r1 = Sheets("sheet2").Range("A3").End(xlDown).Row 
last_column1 = Sheets("sheet2").Range("B2").SpecialCells(xlCellTypeLastCell).Column 
last_column2 = Sheets("sheet3").Range("B1").SpecialCells(xlCellTypeLastCell).Column 
 
For i = 6 To last_r2
Dim lngcolumn8 As Long: lngcolumn8 = 12 
Do Until IsEmpty(Sheets("sheet3").Cells(2, lngcolumn8)) 
    If Sheets("sheet3").Cells(last_r2, 1) > Sheets("sheet3").Cells(last_r2 - 1, 1) Then 'if the current date is larger than the previous one then
        Sheets("sheet3").Cells(last_r2 - 1, lngcolumn8).Copy 
        Sheets("sheet3").Cells(last_r2, lngcolumn8).Insert shift:=xlDown 
        lngcolumn8 = lngcolumn8 + 4 
    End If 
Loop 
 
Dim lngloop As Long: lngloop = 12 
Do Until IsEmpty(Sheets("sheet3").Cells(1, lngloop)) 
    For Each cell In Sheets("sheet3").Range(Sheets("sheet3").Cells(last_r2, 9), Sheets("sheet3").Cells(last_r2, last_column2)) 
        If IsEmpty(cell) Then 
            cell = Application.WorksheetFunction.Index(Sheets("sheet2").Range(Sheets("sheet2").Cells(1, 1), Sheets("sheet2").Cells(last_r1, last_column1)), Application.Match(CLng(Sheets("sheet3").Cells(last_r2, 1)), Sheets("sheet2").Range(Sheets("sheet2").Cells(1, 1), Sheets("sheet2").Cells(last_r1, 1)), 0), Application.Match(Sheets("sheet3").Cells(1, lngloop), Sheets("sheet2").Range(Sheets("sheet2").Cells(2, 1), Sheets("sheet2").Cells(2, last_column1)), 0) + 1) 
            lngloop = lngloop + 4 
        End If 
    Next 
Loop 
Next

Display More

And it works

NewbieVBA · May 28th 2016

Re: Combine two Macro Codes together

I just solved my problem.
Thanks for taking the time to look at it

NewbieVBA · May 25th 2016

Re: Combine two Macro Codes together

Hi Skywriter,

Hereby I send you a sample workbook. Hopefully this makes it much clearer.
forum.ozgrid.com/index.php?attachment/69235/
Thanks

NewbieVBA · May 24th 2016

Hi y'all

I am new at VBA and on this forum. Hopefully, you can help me
I have some difficulties with doing the following:
I have written several macros which look up the correct values (from different sheets) with the desired date.
I will share a part of my syntax.

Code

last_r2 = Sheets("sheet3").Range("A3").End(xlDown).Row ' this is the column with the different dates
last_r1 = Sheets("sheet2").Range("A3").End(xlDown).Row
last_column1 = Sheets("sheet2").Range("B2").SpecialCells(xlCellTypeLastCell).Column
last_column2 = Sheets("sheet3").Range("B1").SpecialCells(xlCellTypeLastCell).Column


Dim lngcolumn8 As Long: lngcolumn8 = 12
Do Until IsEmpty(Sheets("sheet3").Cells(2, lngcolumn8))
If Sheets("sheet3").Cells(last_r2, 1) > Sheets("sheet3").Cells(last_r2 - 1, 1) Then 'if the current date is larger than the previous one then
Sheets("sheet3").Cells(last_r2 - 1, lngcolumn8).Copy
Sheets("sheet3").Cells(last_r2, lngcolumn8).Insert shift:=xlDown
lngcolumn8 = lngcolumn8 + 4
End If
Loop


Dim lngloop As Long: lngloop = 12
Do Until IsEmpty(Sheets("sheet3").Cells(1, lngloop))
For Each cell In Sheets("sheet3").Range(Sheets("sheet3").Cells(last_r2, 9), Sheets("sheet3").Cells(last_r2, last_column2))
If IsEmpty(cell) Then
cell = Application.WorksheetFunction.Index(Sheets("sheet2").Range(Sheets("sheet2").Cells(1, 1), Sheets("sheet2").Cells(last_r1, last_column1)), Application.Match(CLng(Sheets("sheet3").Cells(last_r2, 1)), Sheets("sheet2").Range(Sheets("sheet2").Cells(1, 1), Sheets("sheet2").Cells(last_r1, 1)), 0), Application.Match(Sheets("sheet3").Cells(1, lngloop), Sheets("sheet2").Range(Sheets("sheet2").Cells(2, 1), Sheets("sheet2").Cells(2, last_column1)), 0) + 1)
lngloop = lngloop + 4
End If
Next
Loop

Display More

This works perfectly per row, because I add the dates manually.
Now I want this to work automatically, but I have difficulties writing the syntax.

I have a macro code which automatically fills the dates between the last updated date and the current date (see second code).
With the third code, I tried to see if it possible for each new date that the amount of the previous date is added with 4:

Code

StartDate = Sheets("sheet3").Range("A6").Value
EndDate = Date
NoDays = EndDate - StartDate + 1
Sheets("sheet3").Range("A6").Value = StartDate
Sheets("sheet3").Range("A6").Resize(NoDays).DataSeries Rowcol:=xlColumns, Type:=xlChronological, Date:=xlWeekday, Step:=1, Stop:=EndDate, Trend:=False


Dim i As Long: i = 2
Do Until IsEmpty(Sheets("sheet3").Cells(i, 1))
If Sheets("sheet3").Cells(i, 1) > Sheets("sheet3").Cells(i - 1, 1) Then
Sheets("sheet3").Cells(i, 2) = Sheets("sheet3").Cells(i - 1, 2) + 4
i = i + 1
End If
Loop

Display More

I have tried to nest the first syntax within the second syntax and switch last_r2 with i, but this did not work.
In short, I want to maintain the functionality of the first syntax combined with the autofill between two dates.

Hopefully, I explained my problem correctly and hopefully someone can help me in the right direction

In advance I would like to thank you for taking the time to read my thread