ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   If structure (https://www.excelbanter.com/excel-programming/433045-if-structure.html)

LiAD
If structure
 
Hi,

Please could some-one give me the If structure to use for VB. Something
very trivial such as

if cell 0<ag4<=25 do a4*3
else if ag425 do a4*10
else do nothing
end

I would like to get the structure then I can try and build the rest of code.

Thanks
LiAD

Jacob Skaria
If structure
 
Sub Macro1()

Dim varTemp As Variant

varTemp = Range("AG4")

If varTemp 0 And varTemp <= 25 Then
MsgBox varTemp * 3
ElseIf varTemp 25 Then
MsgBox varTemp * 10
End If

End Sub

If this post helps click Yes
---------------
Jacob Skaria


"LiAD" wrote:

Hi,

Please could some-one give me the If structure to use for VB. Something
very trivial such as

if cell 0<ag4<=25 do a4*3
else if ag425 do a4*10
else do nothing
end

I would like to get the structure then I can try and build the rest of code.

Thanks
LiAD

p45cal[_84_]
If structure
 

LiAD;470320 Wrote:
Hi,

Please could some-one give me the If structure to use for VB.
Something
very trivial such as

if cell 0<ag4<=25 do a4*3
else if ag425 do a4*10
else do nothing
end

I would like to get the structure then I can try and build the rest of
code.

Thanks
LiAD
or:Sub blah()
Select Case Range("AG4")
Case Is 25
x = Range("A4") * 10
Case Is 0
x = Range("A4") * 3
End Select
End Sub


--
p45cal

*p45cal*
------------------------------------------------------------------------
p45cal's Profile: http://www.thecodecage.com/forumz/member.php?userid=558
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=129979

LiAD
If structure
 
Hi,

Thanks for this.

When i try to combine this with an autofill code I'm getting a 1004 error -
autofill method of range class failed, its fails on this line:

Range("P217:AC218").AutoFill Destination:=Range("P217:AC" & lnglastRow)

of this code:

Sub Deletemeveryquickly()

Dim varTemp As Variant
varTemp = Range("AF4")
If varTemp 0 And varTemp <= 25 Then

Dim lnglastRow As Long
lnglastRow = ActiveSheet.Range("C217").End(xlDown).Row
If lnglastRow 265 Then lnglastRow = 265
Range("P217:AC218").AutoFill Destination:=Range("P217:AC" & lnglastRow)

ElseIf varTemp 25 Then
MsgBox varTemp * 10
End If

End Sub


Any ideas why that would be failing there?

Thanks
LiAD


"Jacob Skaria" wrote:

Sub Macro1()

Dim varTemp As Variant

varTemp = Range("AG4")

If varTemp 0 And varTemp <= 25 Then
MsgBox varTemp * 3
ElseIf varTemp 25 Then
MsgBox varTemp * 10
End If

End Sub

If this post helps click Yes
---------------
Jacob Skaria


"LiAD" wrote:

Hi,

Please could some-one give me the If structure to use for VB. Something
very trivial such as

if cell 0<ag4<=25 do a4*3
else if ag425 do a4*10
else do nothing
end

I would like to get the structure then I can try and build the rest of code.

Thanks
LiAD

Jacob Skaria
If structure
 
It should be
Range("P217:AC217").AutoFill Destination:=Range("P217:AC" & lnglastRow)

instead of
Range("P217:AC218").AutoFill Destination:=Range("P217:AC" & lnglastRow)

Also check out the value of lngLastRow..

If this post helps click Yes
---------------
Jacob Skaria


"LiAD" wrote:

Hi,

Thanks for this.

When i try to combine this with an autofill code I'm getting a 1004 error -
autofill method of range class failed, its fails on this line:

Range("P217:AC218").AutoFill Destination:=Range("P217:AC" & lnglastRow)

of this code:

Sub Deletemeveryquickly()

Dim varTemp As Variant
varTemp = Range("AF4")
If varTemp 0 And varTemp <= 25 Then

Dim lnglastRow As Long
lnglastRow = ActiveSheet.Range("C217").End(xlDown).Row
If lnglastRow 265 Then lnglastRow = 265
Range("P217:AC218").AutoFill Destination:=Range("P217:AC" & lnglastRow)

ElseIf varTemp 25 Then
MsgBox varTemp * 10
End If

End Sub


Any ideas why that would be failing there?

Thanks
LiAD


"Jacob Skaria" wrote:

Sub Macro1()

Dim varTemp As Variant

varTemp = Range("AG4")

If varTemp 0 And varTemp <= 25 Then
MsgBox varTemp * 3
ElseIf varTemp 25 Then
MsgBox varTemp * 10
End If

End Sub

If this post helps click Yes
---------------
Jacob Skaria


"LiAD" wrote:

Hi,

Please could some-one give me the If structure to use for VB. Something
very trivial such as

if cell 0<ag4<=25 do a4*3
else if ag425 do a4*10
else do nothing
end

I would like to get the structure then I can try and build the rest of code.

Thanks
LiAD

LiAD
If structure
 
I'm looking to copy both lines at a time though, hence the P217 to AC218. I
need one blank line , then one formula lien, repeating, hence why I need both.

Is this not possible?

"Jacob Skaria" wrote:

It should be
Range("P217:AC217").AutoFill Destination:=Range("P217:AC" & lnglastRow)

instead of
Range("P217:AC218").AutoFill Destination:=Range("P217:AC" & lnglastRow)

Also check out the value of lngLastRow..

If this post helps click Yes
---------------
Jacob Skaria


"LiAD" wrote:

Hi,

Thanks for this.

When i try to combine this with an autofill code I'm getting a 1004 error -
autofill method of range class failed, its fails on this line:

Range("P217:AC218").AutoFill Destination:=Range("P217:AC" & lnglastRow)

of this code:

Sub Deletemeveryquickly()

Dim varTemp As Variant
varTemp = Range("AF4")
If varTemp 0 And varTemp <= 25 Then

Dim lnglastRow As Long
lnglastRow = ActiveSheet.Range("C217").End(xlDown).Row
If lnglastRow 265 Then lnglastRow = 265
Range("P217:AC218").AutoFill Destination:=Range("P217:AC" & lnglastRow)

ElseIf varTemp 25 Then
MsgBox varTemp * 10
End If

End Sub


Any ideas why that would be failing there?

Thanks
LiAD


"Jacob Skaria" wrote:

Sub Macro1()

Dim varTemp As Variant

varTemp = Range("AG4")

If varTemp 0 And varTemp <= 25 Then
MsgBox varTemp * 3
ElseIf varTemp 25 Then
MsgBox varTemp * 10
End If

End Sub

If this post helps click Yes
---------------
Jacob Skaria


"LiAD" wrote:

Hi,

Please could some-one give me the If structure to use for VB. Something
very trivial such as

if cell 0<ag4<=25 do a4*3
else if ag425 do a4*10
else do nothing
end

I would like to get the structure then I can try and build the rest of code.

Thanks
LiAD

Jacob Skaria
If structure
 
Place a message box and see whether lnglastRow is less than or equal to 217;
o otherwise your code should work fine

If this post helps click Yes
---------------
Jacob Skaria


"LiAD" wrote:

I'm looking to copy both lines at a time though, hence the P217 to AC218. I
need one blank line , then one formula lien, repeating, hence why I need both.

Is this not possible?

"Jacob Skaria" wrote:

It should be
Range("P217:AC217").AutoFill Destination:=Range("P217:AC" & lnglastRow)

instead of
Range("P217:AC218").AutoFill Destination:=Range("P217:AC" & lnglastRow)

Also check out the value of lngLastRow..

If this post helps click Yes
---------------
Jacob Skaria


"LiAD" wrote:

Hi,

Thanks for this.

When i try to combine this with an autofill code I'm getting a 1004 error -
autofill method of range class failed, its fails on this line:

Range("P217:AC218").AutoFill Destination:=Range("P217:AC" & lnglastRow)

of this code:

Sub Deletemeveryquickly()

Dim varTemp As Variant
varTemp = Range("AF4")
If varTemp 0 And varTemp <= 25 Then

Dim lnglastRow As Long
lnglastRow = ActiveSheet.Range("C217").End(xlDown).Row
If lnglastRow 265 Then lnglastRow = 265
Range("P217:AC218").AutoFill Destination:=Range("P217:AC" & lnglastRow)

ElseIf varTemp 25 Then
MsgBox varTemp * 10
End If

End Sub


Any ideas why that would be failing there?

Thanks
LiAD


"Jacob Skaria" wrote:

Sub Macro1()

Dim varTemp As Variant

varTemp = Range("AG4")

If varTemp 0 And varTemp <= 25 Then
MsgBox varTemp * 3
ElseIf varTemp 25 Then
MsgBox varTemp * 10
End If

End Sub

If this post helps click Yes
---------------
Jacob Skaria


"LiAD" wrote:

Hi,

Please could some-one give me the If structure to use for VB. Something
very trivial such as

if cell 0<ag4<=25 do a4*3
else if ag425 do a4*10
else do nothing
end

I would like to get the structure then I can try and build the rest of code.

Thanks
LiAD

LiAD
If structure
 

Its faulting actually because of the fact that I have alternate empty and
full rows. If I change the code to P218 to AC219 then it will fill in and
stop at row 220, although it should fill in (or i would like it to fill in)
to row 228 in this case.

I guess it needs to search for two empty rows before stopping?

Thanks


"Jacob Skaria" wrote:

Place a message box and see whether lnglastRow is less than or equal to 217;
o otherwise your code should work fine

If this post helps click Yes
---------------
Jacob Skaria


"LiAD" wrote:

I'm looking to copy both lines at a time though, hence the P217 to AC218. I
need one blank line , then one formula lien, repeating, hence why I need both.

Is this not possible?

"Jacob Skaria" wrote:

It should be
Range("P217:AC217").AutoFill Destination:=Range("P217:AC" & lnglastRow)

instead of
Range("P217:AC218").AutoFill Destination:=Range("P217:AC" & lnglastRow)

Also check out the value of lngLastRow..

If this post helps click Yes
---------------
Jacob Skaria


"LiAD" wrote:

Hi,

Thanks for this.

When i try to combine this with an autofill code I'm getting a 1004 error -
autofill method of range class failed, its fails on this line:

Range("P217:AC218").AutoFill Destination:=Range("P217:AC" & lnglastRow)

of this code:

Sub Deletemeveryquickly()

Dim varTemp As Variant
varTemp = Range("AF4")
If varTemp 0 And varTemp <= 25 Then

Dim lnglastRow As Long
lnglastRow = ActiveSheet.Range("C217").End(xlDown).Row
If lnglastRow 265 Then lnglastRow = 265
Range("P217:AC218").AutoFill Destination:=Range("P217:AC" & lnglastRow)

ElseIf varTemp 25 Then
MsgBox varTemp * 10
End If

End Sub


Any ideas why that would be failing there?

Thanks
LiAD


"Jacob Skaria" wrote:

Sub Macro1()

Dim varTemp As Variant

varTemp = Range("AG4")

If varTemp 0 And varTemp <= 25 Then
MsgBox varTemp * 3
ElseIf varTemp 25 Then
MsgBox varTemp * 10
End If

End Sub

If this post helps click Yes
---------------
Jacob Skaria


"LiAD" wrote:

Hi,

Please could some-one give me the If structure to use for VB. Something
very trivial such as

if cell 0<ag4<=25 do a4*3
else if ag425 do a4*10
else do nothing
end

I would like to get the structure then I can try and build the rest of code.

Thanks
LiAD

Jacob Skaria
If structure
 
Try Macro1 from the previous post which gets the last row from bottom....If
that do not suit your requirement then the only way is to loop through cells
and get the last row..

If this post helps click Yes
---------------
Jacob Skaria


"Jacob Skaria" wrote:

Place a message box and see whether lnglastRow is less than or equal to 217;
o otherwise your code should work fine

If this post helps click Yes
---------------
Jacob Skaria


"LiAD" wrote:

I'm looking to copy both lines at a time though, hence the P217 to AC218. I
need one blank line , then one formula lien, repeating, hence why I need both.

Is this not possible?

"Jacob Skaria" wrote:

It should be
Range("P217:AC217").AutoFill Destination:=Range("P217:AC" & lnglastRow)

instead of
Range("P217:AC218").AutoFill Destination:=Range("P217:AC" & lnglastRow)

Also check out the value of lngLastRow..

If this post helps click Yes
---------------
Jacob Skaria


"LiAD" wrote:

Hi,

Thanks for this.

When i try to combine this with an autofill code I'm getting a 1004 error -
autofill method of range class failed, its fails on this line:

Range("P217:AC218").AutoFill Destination:=Range("P217:AC" & lnglastRow)

of this code:

Sub Deletemeveryquickly()

Dim varTemp As Variant
varTemp = Range("AF4")
If varTemp 0 And varTemp <= 25 Then

Dim lnglastRow As Long
lnglastRow = ActiveSheet.Range("C217").End(xlDown).Row
If lnglastRow 265 Then lnglastRow = 265
Range("P217:AC218").AutoFill Destination:=Range("P217:AC" & lnglastRow)

ElseIf varTemp 25 Then
MsgBox varTemp * 10
End If

End Sub


Any ideas why that would be failing there?

Thanks
LiAD


"Jacob Skaria" wrote:

Sub Macro1()

Dim varTemp As Variant

varTemp = Range("AG4")

If varTemp 0 And varTemp <= 25 Then
MsgBox varTemp * 3
ElseIf varTemp 25 Then
MsgBox varTemp * 10
End If

End Sub

If this post helps click Yes
---------------
Jacob Skaria


"LiAD" wrote:

Hi,

Please could some-one give me the If structure to use for VB. Something
very trivial such as

if cell 0<ag4<=25 do a4*3
else if ag425 do a4*10
else do nothing
end

I would like to get the structure then I can try and build the rest of code.

Thanks
LiAD

LiAD
If structure
 
I've found that if I create another col as a ref I can get to work.

Thanks a lot

"Jacob Skaria" wrote:

Try Macro1 from the previous post which gets the last row from bottom....If
that do not suit your requirement then the only way is to loop through cells
and get the last row..

If this post helps click Yes
---------------
Jacob Skaria


"Jacob Skaria" wrote:

Place a message box and see whether lnglastRow is less than or equal to 217;
o otherwise your code should work fine

If this post helps click Yes
---------------
Jacob Skaria


"LiAD" wrote:

I'm looking to copy both lines at a time though, hence the P217 to AC218. I
need one blank line , then one formula lien, repeating, hence why I need both.

Is this not possible?

"Jacob Skaria" wrote:

It should be
Range("P217:AC217").AutoFill Destination:=Range("P217:AC" & lnglastRow)

instead of
Range("P217:AC218").AutoFill Destination:=Range("P217:AC" & lnglastRow)

Also check out the value of lngLastRow..

If this post helps click Yes
---------------
Jacob Skaria


"LiAD" wrote:

Hi,

Thanks for this.

When i try to combine this with an autofill code I'm getting a 1004 error -
autofill method of range class failed, its fails on this line:

Range("P217:AC218").AutoFill Destination:=Range("P217:AC" & lnglastRow)

of this code:

Sub Deletemeveryquickly()

Dim varTemp As Variant
varTemp = Range("AF4")
If varTemp 0 And varTemp <= 25 Then

Dim lnglastRow As Long
lnglastRow = ActiveSheet.Range("C217").End(xlDown).Row
If lnglastRow 265 Then lnglastRow = 265
Range("P217:AC218").AutoFill Destination:=Range("P217:AC" & lnglastRow)

ElseIf varTemp 25 Then
MsgBox varTemp * 10
End If

End Sub


Any ideas why that would be failing there?

Thanks
LiAD


"Jacob Skaria" wrote:

Sub Macro1()

Dim varTemp As Variant

varTemp = Range("AG4")

If varTemp 0 And varTemp <= 25 Then
MsgBox varTemp * 3
ElseIf varTemp 25 Then
MsgBox varTemp * 10
End If

End Sub

If this post helps click Yes
---------------
Jacob Skaria


"LiAD" wrote:

Hi,

Please could some-one give me the If structure to use for VB. Something
very trivial such as

if cell 0<ag4<=25 do a4*3
else if ag425 do a4*10
else do nothing
end

I would like to get the structure then I can try and build the rest of code.

Thanks
LiAD


All times are GMT +1. The time now is 12:40 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com