New to VBA** Assistance - Database creation and monthly updates

Joined
Jan 4, 2018
Messages
1
Hi all, I am fairly new to learning VBA and have only dabbled in basic macro recording/understanding and minor editing of vba code via MOOCs. I would like to leverage these skills and learn more, as I am an analyst and sifting through massive databases is a daily tedious task.

I have created a master database in excel with over 200,000 rows of data. I would like to somehow construct a monthly macro that I can run to populate the current period's rows of data with a corresponding formula.

i.e. - Column A Range A1:A200601, if equal to P1, then populate x formula in column H1-Hxx. Would this be optimal or too time consuming/large?

This is instead of to having formulas populated through all 12 periods, which caused severe file crashes and I was just unable to work with it. Any help or advice is much welcomed please advise.:confused::confused:
 

Some videos you may like

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

Logit

Well-known Member
Joined
Aug 31, 2016
Messages
3,865
.
If I understand your requirements, this macro will accomplish your goal :

Code:
Option Explicit


Sub CopyYes()
    Dim c As Range
    Dim j As Integer
    Dim Source As Worksheet
    
    Dim myString As String


    ' Change worksheet designations as needed
    Set Source = ActiveWorkbook.Worksheets("Sheet1")
   
    myString = ActiveSheet.Range("P1").Value
    
    j = 2     ' Start copying to row 1 in target sheet
    For Each c In Source.Range("A1:A200601")   ' Do 200,601 rows
        If c = myString Then
           c.Offset(0, 7).Formula = "=Now()"   'edit everthing between the quotation marks to the required formula
           j = j + 1
        End If
    Next c
    
End Sub

You can download a sample functional workbook from : https://www.amazon.com/clouddrive/share/ybVqjKUaZm2WBkyQgrb2OWbX2eLx6pN1KlEikNNVYLm
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,123,467
Messages
5,601,827
Members
414,479
Latest member
Beau the dog

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
Top