After vba use my formula stop working as the vba replaces cell values

Hi ROHAN999
Have you deliberately posted 4 times the same question?.
Or was this caused by the Forum Softwäre?
Did you intend posting 4 times the same question ?
Alan

Originally Posted by ROHAN999

no while i was uploading this threat due to server reset error it got uploaded so many times unintentially

Thanks for letting us know, the Forum software is playing up a bit currently

Hi,

If you delete column C it would not be a great surprise that formulas dependent upon those cells are affected.

Hi ROHAN999
Maybe before deleting column C do something like this

Columns(4).Value = Columns(4).Value

Alan

P.s.
We can only give general ideas if you only give us brief details of your problem
http://www.excelforum.com/showthread...t=#post4494031

Hi ROHAN999
You are not giving us enough infomation.
We cannot help you if you do not give us more detail of the problem.
We can only give general ideas if you only give us brief details of your problem.
http://www.excelforum.com/showthread...t=#post4494031
We need to see your code and / or your workbook.
You must explain to us what you want.

If you delete a column that is referenced in a formula then it is obvious that the formula will error after the column is deleted.

You must tell us what you want to happen.

My solution will replace the formulas with the value they give prior to the column being deleted.

If you wish something else to happen then you must tell us what. For example, you could move the column instead of deleting it_..

    Columns(4).Cut
    Columns(100).Insert Shift:=xlToRight

Alan

_..........”.... from last Post........”....


I still do not understand exactly what you are doing.
I still do not understand exactly what the problem is
I still do not understand what you want.

_.......

Following what you say:

Originally Posted by ROHAN999

...vba is doing..vba delete the entire row where column d is blank
and formulas in cell l are dependent on column d...

Rem 2 ) '......delete the entire row where column d is blank"

Sub WTFROHAN999Wants1() 'http://www.excelforum.com/showthread.php?t=1158483&p=4496780#post4496780
Rem 1 ) Worksheets info
Dim WB As Workbook                                                       ' Dim:  ' Preparing a "Pointer" to an Initial "Blue Print" in Memory of the Object ( Pigeon Hole with a bit of paper or code lines on that can be filled in to refer to a specific Objec of this type ) . This also us to get easily at the Methods and Properties throught the applying of a period ( .Dot) ( intellisense )                     '
Set WB = ThisWorkbook                                                    ' Set now (to This Workbook - the one this code is in), so that we carefull allways referrence this so as not to go astray through Excel Guessing inplicitly not the one we want...         Set: Values are filled at the memory locations and the directions there are specified in the variable "Blue Print "Pointer". In this case a Filled in Blue Print is passed.      http://www.excelforum.com/excel-programming-vba-macros/1138804-help-understanding-class-instancing-cant-set-ws-new-worksheet-intellisense-offers-it-4.html#post4387191Set ws = ActiveSheet ' Alternative to last line, make code apply to the current active sheet, - That being "looked at" when running this code        '
Dim ws1 As Worksheet: Set ws1 = ThisWorkbook.Worksheets("Sheet1")        ''The Worksheets Collection Object of WB is used referrencing by Item string name
Dim ClmEndOfOppressedWorld As Long                                       ''  Long is very simple to handle, - final memory "size" type is known (123.456 and 000.001 have same "size" computer memory ) , and so a Address suggestion can be given for the next line when the variable is filled in.  '( Long is a Big whole Number limit (-2,147,483,648 to 2,147,483,647) If you need some sort of validation the value should only be within the range of a Byte/Integer otherwise there's no point using anything but Long.--upon/after 32-bit, Integers (Short) need converted internally anyways, so a Long is actually faster. )
 Let ClmEndOfOppressedWorld = ws1.UsedRange.SpecialCells(xlCellTypeLastCell).Row '  http://www.excelforum.com/excel-programming-vba-macros/1156821-find-last-row-or-column-why-not-just-use-specialcells-2.html#post4487154
Dim rngD As Range: Set rngD = ws1.Range("D2:D" & ClmEndOfOppressedWorld & "")
Rem 2 ) '......delete the entire row where column d is blank"         - I need to Loop backwards .... When I loop or move forwards or go upwards and then delete something, the next thing I wanted to consider slips down into the “hole” left by the deletion. So when I “move” on I go onto the what is actually the “next next “ and miss out what should have been the next as this next thing has slipped into the “hole” at my present position where I deleted.  Similarly going forward can cause nasty errors, possibly as you have been experiencing if then VBA looks for the thing to consider in a for each. This can occur when it tries to consider something it thinks should be there as it has not considered it yet. ( VBA sometimes makes an internal memory at the start of a Loop of all things and where they are. By "going out of step in a forward step you can confuse it and it looks for something being sure it should be there but it isn’t ( anymore ) !!
Dim cnt As Long '          Loop Bound Variable Count                  -                                          http://www.mrexcel.com/forum/excel-questions/803662-byte-backward-loop-4.html
    For cnt = ClmEndOfOppressedWorld To 2 Step -1 '                   -                          .... Going or Looping “backwards” or "downwards" means the bits that are “behind me going upwards” slip down and fill in the hole I just made. All these bits have been considered already. The next thing I then consider going backwards is the true next thing i wanted to consider.  http://www.mrexcel.com/forum/excel-questions/787428-clear-delete-shift-%3Dxlup-let-y-%3D-y-%96-1-usedrange-rows-count-anomale-2.html
     If ws1.Range("D" & cnt & "").Value = vbNullString Then ws1.Rows(cnt).Delete                      '  Theoretically a specilal value vbNullString is set to aid in quich checks.. But...  http://www.mrexcel.com/forum/excel-questions/361246-vbnullstring-2.html#post44116
    Next cnt
End Sub

Then AFTER is

Row\Col	A	B	C	D	E	F	G	H	I	J	K	L	M
1	1	2	3	4	5	6	7	8	9	10	11	12	13
2		0	#WERT!	12				#WERT!	#WERT!	#WERT!		=VALUE(D2)	0
3		0	#WERT!	45				#WERT!	#WERT!	#WERT!		=VALUE(D3)	0
4		0	#WERT!	66				#WERT!	#WERT!	#WERT!		=VALUE(D4)	0
5

Sheet1

_.. I bet my Willy you do not want that. So you show me now what AFTER you do want. And / or try again to explain.....

Alan

Hi ROHAN999,
Thank you for the File

Sorry, but
I still do not understand what you are doing.
I still do not understand exactly what the problem is
I still do not understand what you want.

_..................

You have supplied a File with some data in column D, and Formulas in column L which are dependent on values in column D

BEFORE: ( Reduced data example of your uploaded File )

Using Excel 2007 32 bit

Row\Col	A	B	C	D	E	F	G	H	I	J	K	L	M
1	1	2	3	4	5	6	7	8	9	10	11	12	13
2		0	#WERT!	12				#WERT!	#WERT!	#WERT!		=VALUE(D2)	0
3		0	#WERT!					#WERT!	#WERT!	#WERT!		=VALUE(D3)	0
4		0	#WERT!	45				#WERT!	#WERT!	#WERT!		=VALUE(D4)	0
5		0	#WERT!	66				#WERT!	#WERT!	#WERT!		=VALUE(D5)	0
6		0	#WERT!					#WERT!	#WERT!	#WERT!		=VALUE(D6)	0
7		0	#WERT!					#WERT!	#WERT!	#WERT!		=VALUE(D7)	0
8		0	#WERT!					#WERT!	#WERT!	#WERT!		=VALUE(D8)	0
9		0	#WERT!					#WERT!	#WERT!	#WERT!		=VALUE(D9)	0
10		0	#WERT!					#WERT!	#WERT!	#WERT!		=VALUE(D10)	0
11

Sheet1

_.........

I still do not understand exactly what you are doing.
I still do not understand exactly what the problem is
I still do not understand what you want.

_....Next post.......

Hi ROHAN999
Thanks for letting us know

In future please help us to help you by giving a good description and code and sample data from the outset
From your initial descriptions it was totally impossible to have had any Idea what so ever of your problem
My efforts were wasted as I was addressing a completely different problem to that indicated by your very brief and incomplete descriptions of the problem

I appreciate that you may have difficulties in communicating in English. But it is essential if you require further assistance to greatly improve on your description of the problem


Also, once again, this_...

Originally Posted by ROHAN999

oh i solved it by changing the position of o7 to o8...

_..tells us nothing. You are again addressing something or some problem or some aspect of some problem. Nobody can follow or understand you


Please remember that we cannot read your mind


Alan