Source
r/visualbasic•
Animosity16

Beginner coder, need help fixing

Program Goals: Spreadsheet includes data from multiple sampling locations for each pollutant. I have a drafted topographic map with circle shapes to indicate each respective sampling location. The program check the values for each sampling location and applies a background color to the shape based on the preset ranges. I have managed to work the basic code out to work for a designated sample location and associated shape. The next stage, I am wanting to add a loop so that it will go through all sampling locations (columns) and change the color of all sampling location shapes. I believe this will be 2 or 3 variables to nest together.

So my questions....

1)If I use a counter for columns like worksheets("data").CountA(rows(2)) will this count all the columns that arent blank in row 2? 2)Currently I am using the shape function for the shapeid, but in order to make it correspond correctly, I think I need to have the shapeid to match the sample location. No biggie, but how would I establish a counter for it to go through all of them? Maybe.... 3) Some columns may not have any data, is there anyway to ensure the program does not stop at those locations? Or should I just add a ND or something and add some sort of code to only consider numerical? 3) Some columns may not have any data, is there anyway to ensure the program does not stop at those locations? Or should I just add a ND or something and add some sort of code to only consider numerical?

Posted Aug 24, 2019, 2:16 AM UTC · edited

112
Songg45•

Make sure you place 5 spaces in front of each line. I can't see literally anything on mobile cause it looks like:

Private Sub CommandButton1_Click()     Dim A1 As Shape     Dim A1Arsenic As Long     Dim ImageWS As Worksheets     Dim DataWS As Worksheets       Set A1Well = ImageWS.Shapes("Oval 13")     Set A1Arsenic = DataWS.Range("B2")     Set ImageWS = Worksheets("Image")     Set DataWS = Worksheets("Data")     If A1Arsenic >= 0.0001 And A1Arsenic <= 11 Then         ImageWS.Select         A1Well.Select             With A1             .Fill             .Visible = True             .ForeColor.RGB = RGB(255, 0, 0)             .Fill.BackColor.RGB = RGB(255, 0, 0)             .Solid         End With         ElseIf A1Arsenic >= 11 And A1Arsenic <= 40 Then           A1.Select            With A1.Fill.Visible = True             ForeColor.RGB = RGB(0, 255, 0)             A1.Fill.BackColor.RGB = RGB(0, 255, 0)                         ElseIf A1Arsenic >= 40 And A1Arsenic <= 100 Then             With A1.Select                 A1.Fill.ForeColor.RGB = RGB(0, 0, 255)                 A1.Fill.BackColor.RGB = RGB(0, 0, 255)                                                         Else                 A1.Fill.Visible                 A1.Fill.ForeColor.RGB = RGB(100, 100, 100)                  A1.Fill.ForeColor.RGB = RGB(100, 100, 100)         End If                                                                     End Sub

2
SreenikethanI•

I've tried formatting it.

1
SreenikethanI•

I've tried formatting the code. And BTW, Some of the "With" statements aren't having their corresponding "End With" statements! Please check!

Private Sub CommandButton1_Click()     
    Dim A1 As Shape
    Dim A1Arsenic As Long
    Dim ImageWS As Worksheets
    Dim DataWS As Worksheets
    Set A1Well = ImageWS.Shapes("Oval 13")
    Set A1Arsenic = DataWS.Range("B2")
    Set ImageWS = Worksheets("Image")
    Set DataWS = Worksheets("Data")
    If A1Arsenic >= 0.0001 And A1Arsenic <= 11 Then
        ImageWS.Select
        A1Well.Select
        With A1
            .Fill
            .Visible = True
            .ForeColor.RGB = RGB(255, 0, 0)
            .Fill.BackColor.RGB = RGB(255, 0, 0)
            .Solid
        End With
    ElseIf A1Arsenic >= 11 And A1Arsenic <= 40 Then
        A1.Select
        With A1.Fill.Visible = True
        ForeColor.RGB = RGB(0, 255, 0)
        A1.Fill.BackColor.RGB = RGB(0, 255, 0)
    ElseIf A1Arsenic >= 40 And A1Arsenic <= 100 Then
        With A1.Select
        A1.Fill.ForeColor.RGB = RGB(0, 0, 255)
        A1.Fill.BackColor.RGB = RGB(0, 0, 255)
    Else
        A1.Fill.Visible
        A1.Fill.ForeColor.RGB = RGB(100, 100, 100)
        A1.Fill.ForeColor.RGB = RGB(100, 100, 100)
    End If
End Sub
1
Animosity16OP•

Thanks so much!

I got it working using much simpler code. Still learning haha.

If I wanted the code to go through each column of data and associated shapes could I do that using a i and j variable?

Like:

Dim i as Integer Dim j as Integer

For i = i to columns.count (Insert above code for first portion of value check) For j = j to worksheets("image").row.count (Second portion) Next I Next j End if

2
SreenikethanI•

Well, that's really amazing, I appreciate it! I have some suggestions.

For C As Integer = 0 To Columns.Count - 1
    For R As Integer = 0 To Worksheets("image").Rows.Count - 1
        'Your code here
    Next R
Next C
  1. You can define the Counter variable in the For statement itself. Like shown above, I have defined the R and C variables in the For statement(s) itself.
  2. The For statement's syntax is: For [variable] = [start value] To [end value] So it's better you put "0" as the start value instead of putting the name of the variable itself.
  3. All lists have an index starting from ZERO (0), NOT ONE (1). So for example, the 5th item will have an index of 4, and the 100th item will have an index of 99. My point is, the End value must be usually 1 less than the total length of the list. So, if you had 10 rows, the end value (for the counter) must be 9.

And also, one more thing, if you don't mind it: If you're making a post/comment in Reddit related to code, there's this feature called "Code block".

If you're using the "Markdown" mode, then you must add 4 spaces in the beginning for every line of code, and also leave a blank line at the top and bottom.

If you're using the "Fancy pants" editor mode, then select the three dots in the formatting bar below, then choose the "Code block" icon (which looks like a "T" on top of a square). Then inside that, paste your code.

1
Animosity16OP•

Ok thanks for feedback. I'll tinker with this and follow up with this post with any comments/questions if that is ok. I'll make sure I post according to requested format next. I'm sorry for that!

2
SreenikethanI•

I'm happy for your progress! :)

1
Animosity16OP•

I have been trying everything but keep getting the error that said I don't have a for statement with next and vise versa but I did. I'm assuming I am typing something wrong.

So for context, I have a spreadsheet with two worksheets. First is the data tab, which has all the information for over 50 different pollutants going down in rows (each will have separate macro button for the image) and the columns are the identifies sample location.

I need the spreadsheet to take the value from first column where data begin and change the shape (id matches shapeid on image tab) color based on preset ranges. I got the color change to work but only for a individual data point. How would I go about making this into a loop. The stop point would be the columns as the data may be empty (though I could add ND in it's place).

1
Animosity16OP•
Private Sub CommandButton1_Click()
Worksheets("Data").Activate
If Worksheets("Data").Range("B2").Value > 0.0001 And Worksheets("Data").Range("B2").Value <= 11 Then Worksheets("Image").Shapes("MWell1").Fill.ForeColor.RGB = vbGreen    
ElseIf Worksheets("Data").Range("B2").Value > 11 And Worksheets("Data").Range("B2").Value <= 40 Then Worksheets("Image").Shapes("MWell1").Fill.ForeColor.RGB = vbYellow    
ElseIf Worksheets("Data").Range("B2").Value > 40 And Worksheets("Data").Range("B2").Value <= 100 Then Worksheets("Image").Shapes("MWell1").Fill.ForeColor.RGB = vbRed        
ElseIf Worksheets("Data").Range("B2").Value > 100 And Worksheets("Data").Range("B2").Value <= 110000 Then Worksheets("Image").Shapes("MWell1").Fill.ForeColor.RGB = vbBlue
End If
Worksheets("Image").Activate   
End Sub
1
Animosity16OP•

This is my working code on individual point. Im sorry for format I am trying to figure out why the 4 spaces didn't fix it.

1
Animosity16OP•
'Question 2'
For i = 3 to worksheets("data").CountA(rows(1))
For j = 3 to worksheets("data").CountA(rows(2)) 
If worksheets("").Range(2,i).Value...Then Worksheets("data").ShapeRange(1,j)...

Next j
Next i
'------------'

Private Sub CommandButton1_Click()
Worksheets("Data").Activate
If Worksheets("Data").Range("B2").Value > 0.0001 And Worksheets("Data").Range("B2").Value <= 11 Then Worksheets("Image").Shapes("MWell1").Fill.ForeColor.RGB = vbGreen    
ElseIf Worksheets("Data").Range("B2").Value > 11 And Worksheets("Data").Range("B2").Value <= 40 Then Worksheets("Image").Shapes("MWell1").Fill.ForeColor.RGB = vbYellow    
ElseIf Worksheets("Data").Range("B2").Value > 40 And Worksheets("Data").Range("B2").Value <= 100 Then Worksheets("Image").Shapes("MWell1").Fill.ForeColor.RGB = vbRed        
ElseIf Worksheets("Data").Range("B2").Value > 100 And Worksheets("Data").Range("B2").Value <= 110000 Then Worksheets("Image").Shapes("MWell1").Fill.ForeColor.RGB = vbBlue
End If
Worksheets("Image").Activate   
End Sub
1