Convert my code from one cell to a full range

**Kodiak450NJ** · 10-08-2020, 07:47 PM

Hello,

I am brand new to VBA and have only gotten this far through some Google searches and a compilation of my research. The code below will work on my first row of data, but I need to expand the loop to work through all rows (my data runs Row 8 through Row 744). My spreadsheet is very busy, but the main columns of concern are BC and BE. Column BE = ( AW / (AZ + BC) ) where AW & AZ are values and I add one by one (1+1...) to BC until it brings BE to be < = 326.49. Again, the below code works for my data in Row 8, but how do I expand it so it does the same loop in BC9:BC744?

Private Sub CommandButton1_Click()

    If Range("BE8").Value > 326.49 Then
        Range("BC8").Value = 1
    End If
    
    If Range("BE8").Value > 326.49 Then
    Do Until Range("BE8").Value < 326.49
        Range("BC8").Value = Range("BC8").Value + 1
    Loop
        
    End If

End Sub

Moderator's note: Please take the time to review our rules. There aren't many, and they are all important. Rule #2 requires code tags. I have added them for you this time because you are a new member. --6StringJazzer

THANK YOU for consideration!

**6StringJazzer** · 10-08-2020, 08:12 PM

I can't test this meaningfully without your file but try this. If it doesn't work then attach your file (see yellow banner at the top of the page).

Private Sub CommandButton1_Click()

   Dim R As Long ' row
    
   For R = 9 To 744
      
      If Range("BE" & R).Value > 326.49 Then
          Range("BC" & R).Value = 1
      End If
      
      If Range("BE" & R).Value > 326.49 Then
      Do Until Range("BE" & R).Value < 326.49
          Range("BC" & R).Value = Range("BC" & R).Value + 1
      Loop
          
      End If
    
   Next R

End Sub

There are two ways to add code tags. One is to select the code part of the text so it is highlighted, then press the "#" button in the edit controls. The other is to simply type in the tags:

[code]
' your code goes here
[/code]

**Kodiak450NJ** · 10-09-2020, 10:29 AM

THANK YOU!!!! This worked perfectly! I also appreciate the guidance with the code tags, as I am just as new to this site as I am to VBA. Thanks again!!

Convert my code from one cell to a full range

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Convert my code from one cell to a full range

Re: Convert my code from one cell to a full range

Re: Convert my code from one cell to a full range

Thread Information

Users Browsing this Thread

Similar Threads

Perform search & return complete text out of a range but not the full cell content

Convert Cell Formula to Convert Text to VBA Code

[SOLVED] need code that will convert a table to a range

Use previous full cell in the subsequent empty cells in a range

[SOLVED] Formula to Convert First/Last Name to 1st Letter of 1st Name + Full Last Name

Define range to select from current cell in a full range (using worksheet_change)

[SOLVED] Range error in code, runs alone but not inside my full program, giving runtime error 1004

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions