new to vb programming

feather1000

New Member
Joined
Feb 18, 2009
Messages
14
basically looking at "if" name in A4 is this then get data from worksheet #4 row b5 - b20 (b5:b20) and put that data into worksheet#1 at b5 - 20. there are to many variables to put into cell formula. :ROFLMAO:
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

StevenD

Active Member
Joined
Nov 17, 2004
Messages
262
Try this.

Rich (BB code):
Dim myRow as Integer
If sheets("sheet 1  name here").range("a4")  = your condition here then
          for  myRow=5 to 20
                  sheets("sheet  1 name here").range("b" & b) = _
                  sheets("sheet  4 name here").range("b" & b)
          next myRow
End If
 

feather1000

New Member
Joined
Feb 18, 2009
Messages
14
Sub mrexcel()
'
' mrexcel Macro
' Macro recorded 3/14/2009 by peter
'
' Keyboard Shortcut: Ctrl+Shift+A
'
Dim myRow As Integer
If Sheets("sheet 1").Range("a4") = "ARLINGTON" Then
For myRow = 5 To 20
Sheets("sheet 1").Range("c" & c) = Sheets("series 100a").Range("b" & b)
Next myRow
End If
End Sub

I'm getting error "variable not defined" on the second b when i run debug. Any ideas ?
 

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
Try

Code:
Sub test()
With Sheets("Sheet1")
    If .Range("A4").Value = "ARLINGTON" Then
        Sheets("series 100a").Range("B5:B20").Copy Destination:=.Range("C5")
    End If
End With
End Sub
 

StevenD

Active Member
Joined
Nov 17, 2004
Messages
262

ADVERTISEMENT

Sorry my mistake.

Code:
Dim myRow as Integer
If sheets("sheet 1").range("a4")  = "ARLINGTON"
          for  myRow=5 to 20
                  sheets("sheet  1").range("b" & myRow) = _
                  sheets("series100a").range("b" & myRow)
          next myRow
End If
 

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
There's no need to loop:

Code:
Sub test()
With Sheets("Sheet1")
    If .Range("A4").Value = "ARLINGTON" Then
        .Range("C5:C20").Value = Sheets("series 100a").Range("B5:B20").Value
    End If
End With
End Sub
 

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,454

ADVERTISEMENT

I agree with VoG, so far I see no need to loop.
However, unless you're always going to be looking only for "ARLINGTON" in A4, then perhaps using case select would be the way to go.

If you can provide say 3 examples of values you might be looking for in A4, and then describe the results you want to see when any of those values are found, then we can show you a meaningful example of what I mean.
 

feather1000

New Member
Joined
Feb 18, 2009
Messages
14
Since i am green behind ears i'm going to ask a few questions so i understand exactly whats going on here. {my input}

Sub test()
With Sheets("Sheet1")
If .Range("A4").Value = "ARLINGTON" Then
.Range("C5:C20").Value{put values into c5:c20 in sheet1} = Sheets("series 100a").Range("B5:B20").Value{getting values from sheet"series 100a b5:b20}..... if so then this is what i'm kinda after as you 'll see here

If .Range("A4").Value = "BANTRIDGE" series 100a c5:c20
If .Range("A4").Value = "BANTRIDGE II" series 100a d5:d20
End If
End With
End Sub
 

feather1000

New Member
Joined
Feb 18, 2009
Messages
14
one thing i did forget to mention which will probibly bite me is that sheet1 A4 is a drop down menu using data validation of

=INDIRECT($A$3)
 

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
If you want to base what happens on the value in A4 you can use e.g.

Code:
Sub test()
With Sheets("Sheet1")
    Select Case .Range("A4").Value
        Case "ARLINGTON": .Range("C5:C20").Value = Sheets("series 100a").Range("B5:B20").Value
        Case "BANTRIDGE": .Range("C5:C20").Value = Sheets("series 100a").Range("C5:C20").Value
        Case "BANTRIDGE II": .Range("C5:C20").Value = Sheets("series 100a").Range("D5:D20").Value
    End Select
End With
End Sub

You can also get this to happen automatically: right click Sheet1's tab, select View Code and paste in

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address(False, False) = "A4" Then
    Application.EnableEvents = False
    Select Case Range("A4").Value
        Case "ARLINGTON": Range("C5:C20").Value = Sheets("series 100a").Range("B5:B20").Value
        Case "BANTRIDGE": Range("C5:C20").Value = Sheets("series 100a").Range("C5:C20").Value
        Case "BANTRIDGE II": Range("C5:C20").Value = Sheets("series 100a").Range("D5:D20").Value
    End Select
    Application.EnableEvents = True
End If
End Sub

Now when you change the selection from the drop-down in A4, C5:C20 should change automatically.
 

Watch MrExcel Video

Forum statistics

Threads
1,133,429
Messages
5,658,740
Members
418,467
Latest member
sc356448

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