Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

VBA loop and if statement

0

how to write code that if value is located in a range, then write text, however, write text for every time value appears in range. data example below on sheet1. on sheet2 I want to populate 'product sold' 3x since the ID of 10 appears on multiple lines (line2, line5, line6). Not sure how to do this. 

 Sheet1   

                 A          B  

Line1        ID         Text

Line2        10         product sold

Line3        11         product out

Line4        12         product discontinued

Line5        10         product sold    

Line6        10         product sold

How I want Sheet2 to look. if sheet1 a2 to a6 = 10 then sheet2 = product sold, product sold is populated the number of times ID=10 is found on sheet1

                A          

Line1       Text

Line2        product sold

Line3        product sold

Line4        product sold

excel
posted by: fiji386 on Thu Aug 6, 2020 at 3:47 pm
Answer
Discuss

Discussion

How would this work? The value to write would be the same value that is found for the first instance of an id number? Have you thought about using a kind of vlookup formula and putting it in the column to the right and when you are done just copy/paste that column over the old one?
don (rep: 1989) Aug 8, '20 at 5:02 am
Add to Discussion

Answers

0

You could try a macro something like this:

Sub Copy_If()

' macro written by WillieD24 Aug. 10/20
' if Col "A" on "sheet1" = 10 then Col "A" on "sheet2" = "product sold"

Dim sht1Row As Integer
Dim sht2Row As Integer
Dim sht1Col_1 As Integer
Dim sht1Col_2 As Integer
Dim sht2Col As Integer

sht1Row = 2
sht2Row = 2
sht1Col_1 = 1
sht1Col_2 = 2
sht2Col = 1

'Sheets("sheet1").Activate

Line1:
If Sheets("sheet1").Cells(sht1Row, sht1Col_1).Value = 10 Then GoTo Line2 Else GoTo Line3

Line2:
    Sheets("sheet2").Cells(sht2Row, sht2Col) = Sheets("sheet1").Cells(sht1Row, sht1Col_2)
    ' or use if it will always be 'product sold' :
    ' Sheets("sheet2").Cells(sht2Row, sht2Col) = "product sold"
    sht2Row = sht2Row + 1
Line3:
sht1Row = sht1Row + 1
If IsEmpty(Sheets("sheet1").Cells(sht1Row, sht1Col_1)) = True Then Exit Sub Else GoTo Line1

End Sub

Cheers   ;-)

posted by: WillieD24 (rep: 557) on Mon Aug 10, 2020 at 9:39 pm
Discuss


Answer the Question

You must create an account to use the forum. Create an Account or Login