-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
Write text "hello" in cell A2 if text is cell B2
- Thread starter Galego
- Start date
Code:
Sub HueHue1()
Range("B2").Value2 = "=IF(A2<>"""",""Hello"","""")"
Range("B2").AutoFill Range(Range("B2"), Range("A2").End(xlDown).Offset(0, 1))
End Sub
Code:
Sub HueHue2()
For i = 2 To Range("A2").End(xlDown).Row
If Not IsEmpty(Cells(i, 1)) Then Cells(i, 2).Value2 = "Hello"
Next i
End Sub
Last edited:
Upvote
0
My Aswer Is This
Well-known Member
- Joined
- Jul 5, 2014
- Messages
- 19,511
- Office Version
- 2021
- Platform
- Windows
Try this:
Code:
Sub Test()
Application.ScreenUpdating = False
Dim i As Long
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
For i = 2 To Lastrow
If Cells(i, 1).Value <> "" Then Cells(i, 2).Value = "Hello"
Next
Application.ScreenUpdating = True
End Sub
Upvote
0
Any idea why it would work in A & B but not as per below.
Sub dispute()
Range("M20").Value2 = "=IF(P20<>"""",""Hello123"","""")"
Range("M20").AutoFill Range(Range("M20"), Range("P20").End(xlDown).Offset(0, 1))
End Sub
I cant get the second part to work.....
Range("M20").AutoFill Range(Range("M20"), Range("P20").End(xlDown).Offset(0, 1))
Sub dispute()
Range("M20").Value2 = "=IF(P20<>"""",""Hello123"","""")"
Range("M20").AutoFill Range(Range("M20"), Range("P20").End(xlDown).Offset(0, 1))
End Sub
I cant get the second part to work.....
Range("M20").AutoFill Range(Range("M20"), Range("P20").End(xlDown).Offset(0, 1))
Last edited:
Upvote
0
Range("M20").AutoFill Range(Range("M20"), Range("P20").End(xlDown).Offset(0, 1))
For starters you will have issues finding out the bottom of the table based on column P
*p is where your data is so that's where you check how big the table is but since you will have blanks in p, this will not be reliable
Pick a column with no blanks that reliably tells your code how big the table is...
you see where I wrote A20? I am assuming your table extends over to column A and column A has some kind of data for every row. This will reliably get you to the bottom of the table then if my assumption is correct. Then I get the end of that and offset it to the right 12 columns... so I land at the end of the table on column M. The Autofill destination must include the cell you are dragging down and the last cell in the range... my code will do what you want if the assumptions i descrivbed are correct but that is up to you.
For starters you will have issues finding out the bottom of the table based on column P
*p is where your data is so that's where you check how big the table is but since you will have blanks in p, this will not be reliable
Pick a column with no blanks that reliably tells your code how big the table is...
Code:
[COLOR=#333333]Sub dispute()[/COLOR]
[COLOR=#333333] Range("M20").Value2 = "=IF(P20<>"""",""Hello123"","""")"[/COLOR]
[COLOR=#333333] Range("M20").AutoFill Range(Range("M20"), Range("A20").End(xlDown).Offset(0, [/COLOR][COLOR=#333333]12[/COLOR][COLOR=#333333]))[/COLOR]
[COLOR=#333333]End Sub[/COLOR]you see where I wrote A20? I am assuming your table extends over to column A and column A has some kind of data for every row. This will reliably get you to the bottom of the table then if my assumption is correct. Then I get the end of that and offset it to the right 12 columns... so I land at the end of the table on column M. The Autofill destination must include the cell you are dragging down and the last cell in the range... my code will do what you want if the assumptions i descrivbed are correct but that is up to you.
Upvote
0
Code:
Sub dispute()
Application.ScreenUpdating = False
Dim i As Long
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, [COLOR=#ff0000]16[/COLOR]).End(xlUp).Row 'this wont be reliable because the last cell of column P might be blank, use another column
For i = 2 To Lastrow
If Cells(i, 16).Value <> "" Then Cells(i, 13).Value = "Hello123...." 'reference column 16 or P to check if that is blank then edit column 13 cell... 13 = M
Next
Application.ScreenUpdating = True
End Subhttps://msdn.microsoft.com/en-us/library/office/ff194567.aspx
read that link, i think that will make things more clear
Last edited:
Upvote
0
Not sure if this is a quick one to do but if instead of blank "" you wanted to enter a formula......
Lets say:
Range("M15").Value2 = "=IF(P15<>"=IF(G15<=0, ""Open"", IF(G15>0, ""Overdue""))",""Hello123testworks"","""")"
Range("M15").AutoFill Range(Range("M15"), Range("A20").End(xlDown).Offset(0, 12))
Obviously that didnt work.
Lets say:
Range("M15").Value2 = "=IF(P15<>"=IF(G15<=0, ""Open"", IF(G15>0, ""Overdue""))",""Hello123testworks"","""")"
Range("M15").AutoFill Range(Range("M15"), Range("A20").End(xlDown).Offset(0, 12))
Obviously that didnt work.
Upvote
0
My Aswer Is This
Well-known Member
- Joined
- Jul 5, 2014
- Messages
- 19,511
- Office Version
- 2021
- Platform
- Windows
I'm not sure if your asking questions or telling us why our code will not work.
I gave you an answer which will do what you asked for but you have never responded back about how that script works for you.
I gave you an answer which will do what you asked for but you have never responded back about how that script works for you.
Last edited:
Upvote
0
